Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jeckstein
Partner - Creator
Partner - Creator

Cumulative Sales for Products over multiple years

Hi.

I have a table that shows Year,Product, and Sales. I need to create a cumulative sales column for each product by different Years.

Screen Shot 2016-10-03 at 10.26.58 AM.png

I need to create set analysis to create the "Cumulative Sales" Column. I have included the equations for cumulative sales in the "Cumulative Sales Equation".

Thanks in advance

15 Replies
jeckstein
Partner - Creator
Partner - Creator
Author

(

(

    Aggr(RangeSum(Above(Sum({1}Revenue), 0, RowNo())), Vintage,[Report Year])

+

Aggr(RangeSum(Above(Sum({1}CashRoyPayout), 0, RowNo())), Vintage,[Report Year])

-

(Aggr(RangeSum(Above(.15*Sum({1<[Product Line]={"Hard Good"}>}Revenue), 0, RowNo())), Vintage,[Report Year]))

-

(Aggr(RangeSum(Above(.10*Sum({1<[Product Line]={"Hard Good"}>}Revenue), 0, RowNo())), Vintage,[Report Year]))

  )

)

Sunny this is the full equation that this was a part of. I had looking for the KPI to be the sum of all of these by vintage and report year.

Does this make sense?

sunny_talwar

Not sure I still understand, from the sample provided above, what is the number that you expect to see in your KPI object?

jeckstein
Partner - Creator
Partner - Creator
Author

If no report year is selected then it should be 68. That would be the sum of all products in year 2015. If i select 2014 then I should get 41. If 2013 is selected then 24. If 2012 then 7.

sunny_talwar

I think all you need for a KPI is Sum(Revenue)

jeckstein
Partner - Creator
Partner - Creator
Author

Sunny,

This is the full equation I am working with

(

(

    Aggr(RangeSum(Above(Sum({1}Revenue), 0, RowNo())), Vintage,[Report Year])

+

Aggr(RangeSum(Above(Sum({1}CashRoyPayout), 0, RowNo())), Vintage,[Report Year])

-

(Aggr(RangeSum(Above(.15*Sum({1<[Product Line]={"Hard Good"}>}Revenue), 0, RowNo())), Vintage,[Report Year]))

-

(Aggr(RangeSum(Above(.10*Sum({1<[Product Line]={"Hard Good"}>}Revenue), 0, RowNo())), Vintage,[Report Year]))

  )

)

/

(

(Sum(Duration * Rate))

+

(Sum(PrimaryAdvPaid))

)

-1

The numerator values are not cumulative but the denominator is. I think this is creating an issues where I cannot simply use the sum(Revenue)

sunny_talwar

I see what you mean. But, by just looking at the expression, it would be difficult to give you a solution. Would you be able to share a sample with the expected output to help you better?