Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Thanks in advance.
Felipe
Maybe my similar issue can help you out. Using AsOfTables is a smart way to control your cumulations.
Perhaps this document helps: Calculating rolling n-period totals, averages or other aggregations
If not, please post a qlikview document that demonstrates the problem.
Maybe my similar issue can help you out. Using AsOfTables is a smart way to control your cumulations.
of course it was @gwassenaar who help me out that time... Thanks again...
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.
Please let me know.
thanks in advance
Felipe
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)))
Dear Linus
I have tried using "before" instead of "above" with no results...(no lines in the chart).
Dear All
I have solved this issue using AsOf tables.
See as reference: Advanced accumulation with nullvalues
In Particular I did this:
Load
CPeriod as CumPeriod,
AddMonths(CPeriod,1-IterNo()) as CPeriod
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.