7 Replies Latest reply: Mar 13, 2015 8:54 AM by Felipe Goitre

# Running/Cumulative Total Calculations

Dear All

I am using QV to calculate impact of the spare part and service costs compared to the production.

My raw data has Product, Month, Year, Supplier, Production, Service & Spares.

For each product, I want to calculate the impact of spare parts and service cost compared to the production level and how it change month by month.

By using a Pivot Chart, I can obtain exactly what I looking for.

When I want to put in a line chart the column "cost / 1000", the cumulative functionality is not working anymore.

The formula I am using for Cost / 1000 is:

if(rangesum(above(sum(Production),0,RowNo()))=0,0,(rangesum(above(sum(Service),0,RowNo()))+rangesum(above(sum(Spare),0,RowNo())))*1000/rangesum(above(sum(Production),0,RowNo())))

Please let me know if the formula used for the line chart should be different.

What I want to obtain is:

Felipe

• ###### Re: Running/Cumulative Total Calculations

Perhaps this document helps: Calculating rolling n-period totals, averages or other aggregations

If not, please post a qlikview document that demonstrates the problem.

• ###### Re: Running/Cumulative Total Calculations

Dear Gysbert

Attached you can find a QV file on which you can see a pivot chart and a line chart.

I will like to put on the line chart the column "Cost / 1000" of the pivot chart but when I use the same expression, the Line chart doesn't show the cumulative results.

If(RangeSum(above(sum(Production),0,12))=0,0,

(RangeSum(above(sum(Service),0,12))+RangeSum(above(sum(Spare),0,12)))*1000 / RangeSum(above(sum(Production),0,12)))

I am not sure if the formula can be used also on a line chart.

Felipe

• ###### Re: Running/Cumulative Total Calculations

Use "before" instead of "above" in a chart.

If(RangeSum(before(sum(Production),0,12))=0,0,

(RangeSum(before(sum(Service),0,12))+RangeSum(before(sum(Spare),0,12)))*1000 / RangeSum(before(sum(Production),0,12)))

• ###### Re: Running/Cumulative Total Calculations

Dear Linus

• ###### Re: Running/Cumulative Total Calculations

Dear All

I have solved this issue using AsOf tables.

See as reference: Advanced accumulation with nullvalues

In Particular I did this:

CPeriod as CumPeriod,

Resident ProdData

While IterNo()<=12;

On the Line chart, I change the Dimension from CPeriod to CumPeriod and the expresion become very simple:

(Sum(Service)+Sum(Spare)) * 1000 / sum(production).

Thanks again.

• ###### Re: Running/Cumulative Total Calculations

Maybe my similar issue can help you out. Using AsOfTables is a smart way to control your cumulations.