Problem with gauge meter

[#1]

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!

[Oleg_Troyansky]

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

[#3]

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!

[Oleg_Troyansky · Accepted solution]

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.

[#5]

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

greetings,

Wouter