topic Re: Gauge for Displaying Average Age in QlikView

Gauge for Displaying Average Age

jmonroe918 — Mon, 13 Jan 2014 21:08:58 GMT

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

Re: Gauge for Displaying Average Age

Nicole-Smith — Mon, 13 Jan 2014 21:14:06 GMT

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:

Re: Gauge for Displaying Average Age

jmonroe918 — Mon, 13 Jan 2014 21:39:02 GMT

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

Re: Gauge for Displaying Average Age

jmonroe918 — Tue, 14 Jan 2014 00:26:00 GMT

Nicole:

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

Jeff

Re: Gauge for Displaying Average Age

Nicole-Smith — Tue, 14 Jan 2014 13:53:26 GMT

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

Re: Gauge for Displaying Average Age

jmonroe918 — Sun, 19 Jan 2014 22:32:09 GMT

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

Re: Gauge for Displaying Average Age

Nicole-Smith — Mon, 20 Jan 2014 13:46:10 GMT

Try this:

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

Re: Gauge for Displaying Average Age

jmonroe918 — Mon, 20 Jan 2014 16:16:23 GMT

Nicole:

That did it. Thanks again.

Jeff

Re: Gauge for Displaying Average Age

jmonroe918 — Mon, 27 Jan 2014 21:48:36 GMT

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

Re: Gauge for Displaying Average Age

Nicole-Smith — Tue, 28 Jan 2014 13:42:56 GMT

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])

Re: Gauge for Displaying Average Age

jmonroe918 — Tue, 28 Jan 2014 14:37:52 GMT

Great, I'll do that. Thanks again.

Jeff