Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
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
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))
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
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! ☺
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