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.

[pnn44794]

Ok.  I'm getting closer.  I figured out that I needed to move CHARGE_MONTH by clicking on it and dragging it, but it still doesn't look like ZH M's.  Additionally, I have 8 months worth of "ThisMonth", 8 months worth of "LastMonth" and 8 months worth of "Change_Percentage", which isn't right either (see latest attached screenshots).  How do I get this display right / corrected?

I also discovered that I need to hide the 201509 data as it's showing up and shouldn't be.  It's a part of the data collection.  Any ideas on that?

Again, thanks in advance.

[#3]

1. To hind 201509, you may change the dimension from CHARGE_MONTH to =if(right(CHARGE_MONTH,2)>=9,null(),CHARGE_MONTH) and then check "Suppress When Value is Null"

2. To modify the background color from dark blue to light blue, you may go to expression and modify RGB function accordingly as shown in the attached picture. To find out the 3 parameters for RGB function, you may open any color selection window, choose the color, and check its RGB.

[pnn44794]

Thanks ZH M!  # 1 worked perfect.  For # 2, I had already done exactly what you suggested, so all good there too.

Did you happen to take a look at the HeatMap-Screenshots_v2 Wor doc?  My pivot table shouldn't look like that, I don't believe.

Thank you for all your help.  I'm definitely learning a few things here.

[pnn44794]

Ah ha!!!!  I had Enabled checked for both ThisMonth and LastMonth.  Unchecking Enabled removed them from the view.  All good now.  A big thank you to everyone and especially ZH M!

[pnn44794]

If I may, one more question about this.  The first row of data has no percentages for the Product Set even though there are dollar amounts that were charged for each month thus far.  Does the following need to be tweaked somehow? Please note that I reenabled ThisMonth so you can see that there are dollar amounts charged.

(sum(CHARGE_AMOUNT)-if(CHARGE_MONTH='201601',sum(CHARGE_AMOUNT),above(sum(CHARGE_AMOUNT))))/sum(CHARGE_AMOUNT)

[pnn44794]

Actually, I think the percentages may be incorrect too.  For example, in the screenshot I provided above, if you look at Application Support for 201601 ($162,103) and 201602 ($150,595), I believe it should be a 7.1% decrease calculated as follows:

162,103 - 150,595 = 11,508

(11,508 / 162,103) * 100 = 7.099% decrease

I'm not sure how to change the Qlikview formula to do this.

[sunny_talwar]

I am not sure why you are using Above here for a pivoted dimension? and where is 162,103 and 150,595 coming from? I am sorry I tried to help you initially but you never responded so I thought you did not want my help. But I think you might need to use Before here

(Sum(CHARGE_AMOUNT)-If(CHARGE_MONTH='201601',Sum(CHARGE_AMOUNT), Before(Sum(CHARGE_AMOUNT))))/Sum(CHARGE_AMOUNT)

or this:

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

[pnn44794]

Sunny, my apologies.  I'm new to the community.  I thought responding back to ZH who also asked for sample data, that you would also be able to see my response.  Do you have to respond back to each person individually every time?  I appreciate the patience and I will try both of your suggestions now.

If you look at the screenshot (HeatMap-Screenshot_v3.docx) attached above, you'll see the numbers I mentioned.

[sunny_talwar]

You don't have to respond to every single one. Don't get me wrong, but at least you can do some kind of acknowledgment my friend.

Anyways, let me take a look at those numbers.

[pnn44794]

Sunny:

The first option you give works best because there is no month prior to 201601 to compare to, so it should be 0% change for all of the Product Classes for 201601.

I think the math is still off some though.  Manually calculating the percent change, I come up with 7.099% decrease, but Qlikview comes up with 7.64% decrease.

I will be offline for a few hours as I need to catch a ride home, but I will be logged on later.