topic Linear gauge, set min / max values depending on dimension value, help please in QlikView

Linear gauge, set min / max values depending on dimension value, help please

Thu, 10 Jun 2010 10:13:14 GMT

Hi,

I have the following pivot table, containing results for a number of different KPIs.

The KPIs 'Actual' and 'Target' values are calculated using formulae which i have stored in variables.

This works fine, results look like this.

Now, i want ot add a linear gauge to the pivot to show how close to the target value we are. I have done this, setting the minimum on the gauge to 0, and the max to the Target value for the KPI, using the following nested IF statement

IF(KPIRef = 'C2', $(vC2Target),
IF(KPIRef = 'C3', $(vC3Target),
IF(KPIRef = 'C4', $(vC4Target),
IF(KPIRef = 'C5', $(vC5Target),
IF(KPIRef = 'C6', $(vC6Target),
IF(KPIRef = 'C7', $(vC7Target),
IF(KPIRef = 'C8', $(vC8Target),
IF(KPIRef = 'C9', $(vC9Target)))))))))

I know this formula works, because i use the same for the Target expression in the table.

When i only have on KPI selected, the linear gauge appears as below:

But when i select more than one KPI, it doesnt like it saying 'Bad min or max value in gauge'

Has anyone else come across this, is it possible to do what i'm trying to do? As i work my way through the KPIs selecting one at a time the gauge seems to update correctly, resetting the max value, but when more than one is picked it errors.

Can anyone advise?

Many Thanks

Linear gauge, set min / max values depending on dimension value, help please

johnw — Sat, 12 Jun 2010 01:21:44 GMT

The gauge minimum and maximum are set once for the entire table, not once for each row of the table. So if more than one KPI is visible in the table, your IF() expression returns null(), resulting in the error.

The way around it is to divide your column expression by the applicable target to get a percentage (Performance = Actual / Target). Set your linear gauge maximum to a maximum of 1, and it will display the linear gauge like you want to see it.

Linear gauge, set min / max values depending on dimension value, help please

Sun, 13 Jun 2010 22:15:23 GMT

John, thanks again for your help

Linear gauge, set min / max values depending on dimension value, help please

Mon, 04 Apr 2011 23:24:19 GMT

Thank you! This solved my same issue in a minute - I had been struggling with it for hours!

Regards,

Helen

Re: Linear gauge, set min / max values depending on dimension value, help please

Anonymous — Tue, 04 Aug 2015 16:16:15 GMT

Thank you.

What would be the Min set to ?