4 Replies Latest reply: Oct 24, 2012 9:42 AM by Carter James

# 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.

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.

Carter

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

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

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

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

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

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

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

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