Qlik Community

Ask a Question

Community Corner

Discussion board for questions about the Qlik Community, its features, sharing information, general discussions and even some fun. This is for both new and longtime community members. Everyone is welcome!

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

Please help..

1 Solution

Accepted Solutions
Not applicable

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

View solution in original post

4 Replies
Not applicable

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

Not applicable

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

View solution in original post

Not applicable

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! ☺

Not applicable

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