topic Re: Need Help With Qlikview Syntax - Calculating % Change in QlikView

Need Help With Qlikview Syntax - Calculating % Change

pnn44794 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Need Help With Qlikview Syntax - Calculating % Change

sunny_talwar — Sun, 28 Aug 2016 21:54:26 GMT

Would you be able to provide a sample and with it your expected output so that we can help you better here

Re: Need Help With Qlikview Syntax - Calculating % Change

rupamjyotidas — Sun, 28 Aug 2016 23:09:28 GMT

You can look at this blog for your heat map

https://qlikfreak.wordpress.com/2014/03/09/heat-map/

Thanks

Re: Need Help With Qlikview Syntax - Calculating % Change

Mon, 29 Aug 2016 02:01:21 GMT

You may use either pivot table or straight table. In the tab, style, you can define the color for traffic light.

Pivot/straight table can be created in many ways. As Sunny suggested, an expected output, maybe created by excel, will be helpful.

Re: Need Help With Qlikview Syntax - Calculating % Change

pnn44794 — Sun, 13 Oct 2019 20:50:48 GMT

The percentage calculation may be a challenge for this as increases and decreases are calculated differently, but maybe not.

Since January is the starting month, it's 0.0%, though I may want to compare it to December and perhaps even do a rolling 12 month comparison.

Maybe a straight table is beeter for this???

Re: Need Help With Qlikview Syntax - Calculating % Change

pnn44794 — Mon, 29 Aug 2016 15:47:06 GMT

Actually, I would be okay simply doing a dollar difference instead of a percent difference, though being able to do both would be handy.

Re: Need Help With Qlikview Syntax - Calculating % Change

Mon, 29 Aug 2016 18:14:11 GMT

Your best bet would be to create a master calendar with all of the time Flags, YTD, ThisMonth, LastMonth, yesterday, last week, etc. and use a set analysis expression to do your calculations.

You can also create Month to date flags that would change on each refresh.

The generic formula would be:

(ThisMonth - LastMonth) / Last Month.

(

sum({<ThisMonthFlag = {'Y'}>} Storage)

sum({<LastMonthFlag = {'Y'}>} Storage)

) /

sum({<LastMonthFlag = {'Y'}>} Storage) -

)

This will give you your percent change as your expression.

Use an if statement

If (Above expression) > -.1, RGB (Green),

etc. etc. etc.

Re: Need Help With Qlikview Syntax - Calculating % Change

pnn44794 — Sun, 13 Oct 2019 20:52:07 GMT

I appreciate all that have replied, but I'm still stuck. Please note, I do have much larger charge amounts.

I suspect I may have to do something different with the data. Thanks in advance.

Re: Need Help With Qlikview Syntax - Calculating % Change

Tue, 30 Aug 2016 02:51:26 GMT

I made a quick one. Is that what you want?

Re: Need Help With Qlikview Syntax - Calculating % Change

pnn44794 — Sun, 13 Oct 2019 20:35:17 GMT

Thank you ZH M! I think we're very close.

So, how are you getting the CHARGE_MONTH to show across the top? Would you mind showing me how to do another pivot table heat map except this time with dollars instead of percent? I would want to color the cells based on the same percent change.

One thing I do realize, I need to use a lighter shade of Blue or no one will be able to read the value. 🙂

Thanks in advance.

Re: Need Help With Qlikview Syntax - Calculating % Change

pnn44794 — Sun, 13 Oct 2019 20:31:20 GMT

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.

Re: Need Help With Qlikview Syntax - Calculating % Change

Wed, 31 Aug 2016 01:52:48 GMT

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.

Re: Need Help With Qlikview Syntax - Calculating % Change

pnn44794 — Wed, 31 Aug 2016 13:31:00 GMT

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.

Re: Need Help With Qlikview Syntax - Calculating % Change

pnn44794 — Wed, 31 Aug 2016 16:05:09 GMT

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!

Re: Need Help With Qlikview Syntax - Calculating % Change

pnn44794 — Sun, 13 Oct 2019 20:28:27 GMT

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)

Re: Need Help With Qlikview Syntax - Calculating % Change

pnn44794 — Wed, 31 Aug 2016 21:06:30 GMT

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.

Re: Need Help With Qlikview Syntax - Calculating % Change

sunny_talwar — Wed, 31 Aug 2016 21:26:15 GMT

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)

Re: Need Help With Qlikview Syntax - Calculating % Change

pnn44794 — Wed, 31 Aug 2016 21:35:46 GMT

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.

Re: Need Help With Qlikview Syntax - Calculating % Change

sunny_talwar — Wed, 31 Aug 2016 21:39:58 GMT

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.

Re: Need Help With Qlikview Syntax - Calculating % Change

pnn44794 — Wed, 31 Aug 2016 21:46:52 GMT

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.