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: 
Not applicable

need to simplify my expression creating: sum accumalted sales per year using interRecords function

Hi Guys,

If created an expression that gives me the expected accumateled sales starting 2011. (see screenshot). What this expression does is: sum(Sales) of  a year + the sum(Sales) over the previous years.

accumulated sales.JPG

example:

11.684 + 16.442 = 28.126 (2012) expected accumalted sales in 2012

28.126 + 24.340 = 52.466 (2013) expected  accumalted sales in 2013

I 'Solved' this using the following expression:

if(Year=2011,

sum(Sales) ,

    if(Year=2012,

    sum(Sales)  + before((sum(Sales) ),1),

    if(Year=2013,

    sum(Sales) + before((sum(Sales) ),1) + before((sum(Sales) ),2),

    if(Year=2014,

    sum(Sales)  + before((sum(Sales) ),1) + before((sum(Sales) ),2) + before((sum(Sales) ),3),

    if(Year=2015,

    sum(Sales)  + before((sum(Sales) ),1) + before((sum(Sales) ),2) + before((sum(Sales) ),3)  + before((sum(Sales) ),4),

    if(Year=2016,

    sum(Sales)  + before((sum(Sales) ),1) + before((sum(Sales) ),2) + before((sum(Sales) ),3)  + before((sum(Sales) ),4)

    +     before((sum(Sales) ),5)

                     ))))))   

As you can see there's just to many if statments, which makes it to difficult to manage if another year is added.

My 2 questions are:

  •      How can I simplify my expression? Can I use before once where it looks that the sum(Sales) of all the previous years and adds it with the sales of the year of the colum?
  • Can I create one table like the screenshot above?

I've attached a qlikview sample.

Thanks in advance!

Carter

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Ok, try this then: RangeSum(Before(sum(Sales),0,ColumnNo()))


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Gysbert_Wassenaar

If you hadn't made a crosstable, the field year would not be text. It would then be a lot simpler to calculate the cummulative sales over years. See attached example


talk is cheap, supply exceeds demand
Not applicable
Author

Hi Gysbert,

Thanks for your help! The 'problem' with your solution is that you have to create an expression for each year. If my dimension would have 10 years or more I need to create at least 10 expression.

I've formated the field year from text to year.

Cheers

Carter

Gysbert_Wassenaar

Ok, try this then: RangeSum(Before(sum(Sales),0,ColumnNo()))


talk is cheap, supply exceeds demand
Not applicable
Author

Cool!!! Thanks Gysbert! Which I knew about the rangesum function. Learned something new today