Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
pnn44794
Partner
Partner

Need Help With Qlikview Syntax - Calculating % Change

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.

 

 

1 Solution

Accepted Solutions
sunny_talwar

I think this should def. work

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

View solution in original post

29 Replies
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
Specialist
Specialist

You can look at this blog for your heat map

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

Thanks

Not applicable

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
Partner
Partner
Author

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
Partner
Partner
Author

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

Not applicable

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
Partner
Partner
Author

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.

Not applicable

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

pnn44794
Partner
Partner
Author

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.