topic Problem with gauge meter in QlikView

Problem with gauge meter

Mon, 11 Apr 2011 14:36:09 GMT

Hi all,

I am trying to build a gauge meter, according to the example of John Witherspoon. However my meter doesn't display the arrow.

I have the percentage, I have the YTD and the target. However the meter doesn't want to point at -35%.

I probably have not understood it correctly, but these are my formula's :

Meter or indicator (expression):

sum(if(YTD_FLAG, OMZET))-sum(if(TARGET_YTD_FLAG, TARGET)))/sum(if(TARGET_YTD_FLAG, TARGET))

Percentage formula:

num((sum(if(YTD_FLAG, OMZET))-sum(if(TARGET_YTD_FLAG, TARGET)))/sum(if(TARGET_YTD_FLAG, TARGET )),'#,#%')

Anybody an idea? Unfortunately the data is so complex so I can't post a dataset.

Thank you!

Problem with gauge meter

Oleg_Troyansky — Mon, 11 Apr 2011 14:50:55 GMT

It sounds silly, but - have you selected "Show Needle" as your Indicator mode?

As a side comment - if you already have YTD flags, don't use IF() conditions, they are killing performance. Either use Set Analysis, or at least replace IF with simply multiplying by the flag. See this wiki page:

http://community.qlik.com/wikis/qlikview-wiki/using-flags-to-avoid-if-formulas.aspx

Problem with gauge meter

Mon, 11 Apr 2011 15:17:27 GMT

Hi Oleg,

Thank you! It wasn't the solution. But when I restarted the dashboard to see if that wasn't the case, the needle suddenly was there.

So it works now!

Question about your side comment.

I saw in the examples -1 and 1 as flags. Which one to use and why. I have created the flags in my database, so I 'load' the data, including a YTD_FLAG which contains a 1 or a 0.

So, how would I remove the IF from this formula?

sum(if(YTD_FLAG, OMZET))-sum(if(TARGET_YTD_FLAG, TARGET)))/sum(if(TARGET_YTD_FLAG, TARGET))

Thanks for the help and time!

Problem with gauge meter

Oleg_Troyansky — Tue, 12 Apr 2011 04:59:00 GMT

Wouter,

you may see Flag = -1 because that's the numeric value of "true" in QlikView. So, if people are using boolean expressions without reversing the sign, they get -1.

If your flag is 1 or 0, you can do one of the two:

1. Using Set Analysis:

(sum({<YTD_FLAG = {1} >} OMZET)-sum( {<TARGET_YTD_FLAG={1}>} TARGET))/

sum({<TARGET_YTD_FLAG={1}>} TARGET)

2. Or, if you want to avoid the SA syntax, simply this:

(sum(YTD_FLAG*OMZET)-sum(TARGET_YTD_FLAG * TARGET))/sum( TARGET_YTD_FLAG* TARGET)

When you multiply your values by the flag, those values associated with 1 will get included and those values associated with 0 will get excluded.

If you have a lot of data, Set Analysis is faster. If not, then multiplying by the flag is OK, but much better than 3 IF formulas.

Problem with gauge meter

Tue, 12 Apr 2011 08:25:59 GMT

Thanks!!! That's a lot of help!!!

greetings,

Wouter