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]

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

[rupamjyotidas]

You can look at this blog for your heat map

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

Thanks

[#4]

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.

[pnn44794]

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

[pnn44794]

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

[#7]

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.

[pnn44794]

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.

[#9]

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

[pnn44794]

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.