topic KPI meter with calculated values in QlikView

KPI meter with calculated values

amber2000 — Mon, 27 Jul 2015 15:28:55 GMT

Hello everyone,

I'm Trying to create a KPI meter with a gauge chart.

Through use of variables I want to show de full 100% of the percentage in grey and when the % is higher or equal to 99.96 the meter has to be green, else red by using the variables

If my percentage is 99.97 the meter must be green for that amount en the rest of the 100% (0.03 %) has to be grey.

I'm trying to achief that in the presentation tab with the use of Segments Setup.

Segment 1 --> Calculated Color is the result of the variable (vKPI_Foute_Ladingen_Color)

if(num((1-(count((if([DelayC]=99,[Jit_Transport_Nbr])))/count((if([Jit_Transport_Nbr]<999,[Jit_Transport_Nbr]))))) *100 , '##.##%') >= 99.96, Green(), LightRed())

Segment 2 is the rest of the 100% that needs to be grey (0.03).

I'm sending My effort in an attachement, it's not completely like I want it but I can't find what I'm doing wrong.

I've also added my txt file that's been loaded into the script.

Can anybody help me out please?

Kind regards,

Ambertje

Re: KPI meter with calculated values

a_mullick — Mon, 27 Jul 2015 16:12:15 GMT

Hi,

I've made a couple of minor changes to your guage...I think it gives you what you want...

Azam

Re: KPI meter with calculated values

amber2000 — Tue, 28 Jul 2015 10:35:47 GMT

Hi Azam,

It's working indeed, Thank you so much.

Now I would like to ask you why you have set the Gauge settings Max to 105 and why the the variable in segment1 has ( ) and the variable in segment2 has no ( )

Kind regards,

Ambertje

Re: KPI meter with calculated values

a_mullick — Tue, 28 Jul 2015 10:58:07 GMT

Hi,

The Guage settings Max was set to 2 in your test example, but the expression was factoring the ratio by 100 to generate a % value, so asumming there is nothing odd in your data the gauge should always range from 0 to 100. The small issue is that 0.03% is very small, so when you set the max size to 100, you can't really see the grey, I set it to 105 to emphasize the grey, although 101 will work ok as well - however, you need to bear this in mind...

As for segment 2, there was no reason about leaving the () off, with or wothout is fine in this case, the main thing was that you originally defined the segment 2 lower bound with you colour variable rather than your expression variable.

Thanks,

Azam

Re: KPI meter with calculated values

amber2000 — Tue, 28 Jul 2015 11:24:23 GMT

Thanks a million for your help I understand it perfectly now

Re: KPI meter with calculated values

amber2000 — Tue, 28 Jul 2015 14:08:58 GMT

Dear Azam

Sorry to bother you again but after serious testing I saw a problem which I can't solve on my own.

When I select a single Calendar_Day 31/01/2015 (where the result is a 100%) the meter shows 1e+004%.

Any Idea how I can solve this please???

Kind regards,

Ambertje

Re: KPI meter with calculated values

a_mullick — Tue, 28 Jul 2015 14:56:32 GMT

Hi Ambertje,

No problem...

I think I can see the issue, but you'll need to double check and make sure I'm right.

I've altered your basic expression to be like this:

=num((1-(count((if([DelayC]=99,[Jit_Transport_Nbr])))/count((if([Jit_Transport_Nbr]<999,[Jit_Transport_Nbr]))))) , '##,##%')

I've removed the '*100', because the '%' in the number format sort of does this for you i.e. factors a number by 100...I didn't appreciate this at first glance, because the numbers sort of looked ok. As a consequence the guage is not now set from 0 to 1 (Min and Max).

It also looks like you're in Northern Europe somewhere, so I think your default settings are ',' (comma) for a decimal seperator, and '.' (stop) for thousands seperator.

Hope that helps,

Azam