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

[#1]

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.

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

[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

[#3]

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 · Accepted solution]

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

[#5]

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