12 Replies Latest reply on Apr 5, 2016 2:36 AM by koushik.neelakantha

# Bar Visualization form cumulative KPI

Hello ,

I have day num till 23(Day1 till Day23)..my sales are not present for Day 21,22 and 23 .Taken a bar chart.

I am getting correct cumulative values till D20, but for Day 21,22,23 I am getting  Day 20 values for Day 21,22,23.( because my sales are not present for those)

I should not get like that..i need to get zero for Day 21,22,23.

Kindly help me.

Regards,

Koushik.

• ###### 1. Re: Bar Visualization form cumulative KPI

If you're calculating cumulative values wouldn't it be correct for the value to remain static (i.e. same as day 20) if sales didn't change - which is the case if there were no sales? What would happen on day 24 if there were sales of 100? What value would be expected to be shown as part of the cumulative measure?

• ###### 2. Re: Bar Visualization form cumulative KPI

Hello Watson,

Please find the attached sheet. I have 23 days overall

My sales are present till Day 21, I Have to see Day 22 and Day 23 in the Columns with Zero

Regards,

Koushik.

• ###### 3. Re: Bar Visualization form cumulative KPI

What is the logic you want to use to filter out days? Where sales are 0/null? In which case put Sum(sales) on the filter and exclude 0/null.

• ###### 4. Re: Bar Visualization form cumulative KPI

Hello Watson,

I have two columns. One is DAY_NUM and other is Sales(Cumulative) measure.

Day_num has Days till 23 like D1,D2,D3......D23. But my cumulative sales are present till Day 21.

I need to show sales till Day 1 to Day 21 in addition with Day 22 and Day 23 sales as zero/null in a BAR CHART.

Regards,

Koushik

• ###### 5. Re: Bar Visualization form cumulative KPI

How is your data structured? It contains the daily amounts OR it contains the cumulative amount?

How do you identify in the data which dates should display 0? Would all past days have some sales?

• ###### 6. Re: Bar Visualization form cumulative KPI

Hello Watson,

My sales are cumulative Day wise. In the below screen shot i have cumulative sales till D22. But as the sales are cumulative, I am getting same value for Day 23 as well. For Day 23 i need to show zero/null because my sales are not present for that day.

we are going to refresh the sales on daily basis.

• ###### 7. Re: Bar Visualization form cumulative KPI

Can you please supply your data? You could be constructing this in many different ways, each requiring a different solution.

• ###### 8. Re: Bar Visualization form cumulative KPI

Hello watson,

Regards,

Koushik

• ###### 9. Re: Bar Visualization form cumulative KPI

This is made tricky as I'm not understanding your data - I don't know whether the FBP_BALANCE_DAILY should be included when there are no sales or not. Therefore I've assumed if there are any sales in a day the FBP_BALANCE_DAILY should be included and am filtering out those days where the sum of sales for the entire day is 0.

Create this calculated field: {FIXED [Day Num]:SUM([Local Sales])}

Drag it to the filters shelf, filter for SUM and filter Special for Non-null values. Those without any sales in a day - i.e. NULL - will have a sum of sales of NULL for the entire day and will be removed. In your data that would mean day 23 is filtered out.

• ###### 10. Re: Bar Visualization form cumulative KPI

In this case Day 23 is getting disappeared and  my FBP ( Line chart) is also showing till Day 22.

This should not be the case. My fbp should show till Day 23 and my sales should show till Day 22.

Regards,

Koushik

• ###### 11. Re: Bar Visualization form cumulative KPI

OK, expanding on what was done above, keeping the calculated field 'Calculation 1' just created, create another calculated field and give it the formula:

IF ISNULL(SUM([Calculation1]))  THEN 0 ELSE RUNNING_SUM(SUM([Local Sales])) END

Use that in place of your current running sum of local sales and Day 23 will be blank.

1 of 1 people found this helpful
• ###### 12. Re: Bar Visualization form cumulative KPI

Genius...Thanks a ton