Gauge for Displaying Average Age

[jmonroe918]

I am new to QlikView and developing my first dashboard.

I am trying to create a gauge that displays the Average Age of the open records in the table (in this case "complaints"). I want the gage to split the display into 3 sections "<30" (green), 30-60 (yellow) and >60 (red). The age field is called "Age (Days)" and the open records have a "Status" that does not equal "Closed".

I've tried and here is my result. I have not filtered the out the closed records and I am having trouble with "text in chart" where I would like to display actual average age.

Thanks for the help.

Jeff

[Nicole-Smith · Accepted solution]

In your expression, you need set analysis that looks like this:

avg({$-<Status={'Closed'}>}[Age (Days)])

Put the same expression here to display it as text in the chart:

[jmonroe918]

Nicole:

Thank you very much for the quick response. Just to verify on the expression. Is that filtering out closed records, or is it including only closed records. I trying to decipher the code to see where the "not equals" logic is.

And can you tell me how I can move the text thas overwriting on the gauge?

Thanks.

Jeff

[jmonroe918]

Nicole:

I looked up Set Analysis and saw that it is correct. Thanks again.

Jeff

[Nicole-Smith]

Hi Jeff,

I'm glad you were able to figure out what the set analysis was.  As for moving the text, if you hold CTRL+Shift, red lines will appear and you can move stuff around.

Good Luck!

Nicole

[jmonroe918]

Nicole:

Another question; I'd like to add another condition to the expression. I want the average age of CAPA records that are not "Closed" , and Origin contains "Audit". I'm trying the expression below, but is not working.

='Avg Age (Days): '& Num(Avg({$-<CAPA.Status={'Closed'}>} and {$<[CAPA.Origin]={'*Audit*'}>}[CAPA.Age (Days)]), '#.')

Thanks.

Jeff

[Nicole-Smith]

Try this:

='Avg Age (Days): '& Num(Avg({$<[CAPA.Origin]={'*Audit*'}>-<CAPA.Status={'Closed'}>}[CAPA.Age (Days)]), '#.')

[jmonroe918]

Nicole:

That did it. Thanks again.

Jeff

[jmonroe918]

Hi Nicole:

I was hoping you might be able to verify for me the following set analysis expression to ensure I have it right:

     Count ({$<CAPA.Origin]={'*Audit*'}>*<CAPA.Status={'Closed'}>} [CAPA.CAPA No])

I want to count up the number of recoreds (CAPA No) where the Origin contains "Audit" and the Status = Closed.

Thanks.

Jeff

[Nicole-Smith]

You can write this one a bit easier.  If you want both conditions, you can just separate them with a comma:

Count({$<CAPA.Origin={'*Audit*'},CAPA.Status={'Closed'}>} [CAPA.CAPA No])