topic Re: Monthly Running cumulative total in Water Cooler

Monthly Running cumulative total

Tue, 25 Mar 2014 15:47:24 GMT

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

Please help..

Re: Monthly Running cumulative total

Tue, 25 Mar 2014 16:33:43 GMT

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

Tue, 25 Mar 2014 16:46:05 GMT

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

Re: Monthly Running cumulative total

Tue, 25 Mar 2014 17:32:29 GMT

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

Tue, 25 Mar 2014 18:12:14 GMT

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