0

# Cut off the running sum in a crosstab/chart

Jan 17, 2017 at 10:22 AM

72

Former Member

Hi,

We are showing a model life cycle for a period of 36 months, the first month is first sale of the model second is second sale and so on.

I have a crosstab/Line bar with following facts and dimension

1. Model

2. Month Count (1 2, 3 till 36 where 1 tells us the first sale of a product)

3. Defect Ratio =RunningSum(defect reported)/Runningsum(Total Sales)

I am comparing different models in a line chart as attached.

capture1.jpg

Problem : The Data for one of the model is till 17th month count only in the database and rest of the model number has data till 36 month but the chart is showing flat line even after there is no data for that model number after monthcount 17. Can we break the running sum by any chance, I calculated max (monthcount) for each model number and tried resetting running sum at max (month count) but that is not working.

We want the line to stop at month count where there is no data for the model number.

capture1.jpg (35.5 kB)

Sateesh Kumar Bukkisham Jan 17, 2017 at 01:27 PM
0

Hi ,

Try making "NULL" for discontinued values .

=if(value<>0;value) leave else part , so it will make the value as NULL .

Regards

Sateesh

Share
AMIT KUMAR
Jan 17, 2017 at 10:42 AM
0

try this formula.

=If([defect reported]=0) Then 0 Else RunningSum([defect reported])

same for other.

Show 3 Share
Former Member

Thanks Amit for the reply but it is nt working , I Changed the formula for defects and total sales like this

if (sales) =0 then 0 else runningsum(sales;row;modelno) but it is not working and it gives div/0 error also ...

Former Member

is this not resetting the values? don't use the reset dimension and see

if (sales) =0 then 0 else runningsum(sales)

if it's working and issue with div/0 error then you can try this.

use this.

=if(iserror(Defect Ratio)) then 0 else Defect Ratio

Former Member
AMIT KUMAR

No Amit it is not working as sales/defects can be zero anytime ,

like month 1 sales =5 running sales 5

month 2 sales 10 running sales 15

month 3 sales 0 running sales 15 , but your logic will make the running sum 0 here and that is what happened in the report ...

thanks,

Charu

Former Member Jan 19, 2017 at 06:32 AM
0

Thanks Sateesh, it worked ...I used where amount>=0 then runningsum left the else part ...

Share