Need Help With Qlikview Syntax - Calculating % Change

[pnn44794]

Hello:

I need to create a heat map using a pivot table (assuming this is the best option, but I'm certainly open to other suggestions) that will show the percent billing change from one month to another, by manager and charge type / category.  The idea is to show good or blue when no greater than 5% above the previous month, amber when >5% and less than 9.9%, red when 10% or greater and green if less than the previous month.

My challenge is two fold. One is getting the pivot table (or other option) created correctly.  Two is performing the calculation with the correct syntax and should it be put in a variable???

If you have not guessed by now, I a new to Qlikview, so I appreciate your patience.  Thanks in advance.

[sunny_talwar]

In fact, I think you need this:

Alt(Before(Sum(CHARGE_AMOUNT)), 0)/Sum(CHARGE_AMOUNT) - 1

[sunny_talwar]

I am trying to avoid doing a change in if statement every time a year changes. I think Before(Sum(CHARGE_AMOUNT)) = Null and Alt might be able to force it to 0.

But when you get a chance, try this:

Alt(Before(Sum(CHARGE_AMOUNT)), 0)/Sum(CHARGE_AMOUNT) - 1

[pnn44794]

Ok.  Tried that.  Same results and the percentage is not correct.

[sunny_talwar · Accepted solution]

I think this should def. work

Alt(Sum(CHARGE_AMOUNT)/Before(Sum(CHARGE_AMOUNT)) - 1, 0)

[pnn44794]

That did it!  Thank you very much.  Can you tell me what this equation says in English (it will help me learn)?  I think it says, at least partially, get the summed charge amount for the current month and divide it by the summed charge amount from the previous month????  I definitely don't know what the - 1, 0 does.

[sunny_talwar]

So in plan english the expression is dividing current column's Sum(CHARGE_AMOUNT) and divide it by previous column's Sum(CHARGE_AMOUNT). But for the first column when previous Sum(CHARGE_AMOUNT) is not available you get null and Alt functions kicks in where it picks the 1st non-null value from the list of things you provide in there.

[pnn44794]

Great explanation!  Thank you for all your help.

And thank you to the others for their assistance as well.

[sunny_talwar]

I am glad we were all able to contribute to your learning in one way or the other

[pnn44794]

Ok.  So using =if(right(CHARGE_MONTH,2)>=09,null(),CHARGE_MONTH) will certainly hide CHARGE_MONTH = 201509, but it also hides 201609 and that's not what I want to have happen.  This is my fault as I don't think I explained my data very well.

I have data for Charge Months 201509 to 201512.  201509 to 201512 is what I wanted to hide.  I only want to include 201601 - 201612 (once all of 2016 is available) and then when I go into 2017, I'll start with just 201701, will then add, automatically hopefully, 201702, 201703, etc.

Make sense?  Thanks in advance.

[pnn44794]

I figured it out.