4 Replies Latest reply: Mar 25, 2014 2:12 PM by Ajay Krishnan Prabhakaran

# Monthly Running cumulative total

Hi -

I am new to Qlikview and I need help to calculate Running cumulative totals using a Pivot table as well as returning the MONTH (Index function in Excel) when a person actually reaches their Target (Sales Goal) for the year. I have been add Month 1 + Month 2 = Month 3, but there must be a better way to get the same thing using an expression.

Please see attached excel file for reference.  I am trying to get A (cumulative months) and B (whether they meet Sales met their target, etc.)

• ###### Re: Monthly Running cumulative total

try this approach...

for part A,

you can write your expression something like: rangesum(before( sum(Sales),0,Month))

month is number of the month (if your calendar month use num(Month))

its difficut to get the part B, but as an alternative you can include a background expression

like below, which highlights all those values exceeding targets. you can still modifly the expression to highlight the one month (ex: Aug)

=if(rangesum(before( sum(Sales),0,Month))>=Target,rgb(255,150,300),rgb(255,255,255))

• ###### Re: Monthly Running cumulative total

Hey Suzanne,

Attached with answer. I thing i did was find the running total starting from Jan to Dec. Also I created a variable in the back-end to set the Target amount. Please go through the code and the tables created on the front end.

Thanks

AJ

• ###### Re: Monthly Running cumulative total

I tried both logics and they do work.  The additional logic that Ajay did push me to the finish line!. Thank You to you both! ☺

• ###### Re: Monthly Running cumulative total

Hey Suzanne,

Can you close the post by marking the answers as helpful or answered, so that it would be helpful for others as well

Thanks

AJ