topic Gauge in QlikView

Gauge

kev6brown — Wed, 25 Nov 2020 16:16:04 GMT

I'm looking to create a gauge that works differently depending on what has been selected from the calendar.

so, if [CalMonth] is selected the calculation needs to be

Count((BedDaysPatientID))
/
((Sum({<CalDay={1}>}Data)/2)*Count(CalDay))

if [CalDay] is selected the calculation will be

Count(Distinct(BedDaysPatientID))
/
((Sum(Data)/2)*Count(CalDay))

How do i write the calculation based on the above?

Thanks

Kev

Re: Gauge

vmoreno2605 — Thu, 04 Jul 2019 14:01:56 GMT

if(GetSelectedCount(CalDay)>0,Count(Distinct(BedDaysPatientID))/((Sum(Data)/2)*Count(CalDay)),

if(GetSelectedCount(CalMonth)>0,Count((BedDaysPatientID))/((Sum({<CalDay={1}>}Data)/2)*Count(CalDay))

)

What will happen is when you select callmonth and callday it's gonna use callday calculation.

I hope this works

Re: Gauge

vmoreno2605 — Thu, 04 Jul 2019 14:03:01 GMT

sorry, i missed the last ")"

if(GetSelectedCount(CalDay)>0,Count(Distinct(BedDaysPatientID))/((Sum(Data)/2)*Count(CalDay)),

if(GetSelectedCount(CalMonth)>0,Count((BedDaysPatientID))/((Sum({<CalDay={1}>}Data)/2)*Count(CalDay))

) )

Re: Gauge

kev6brown — Thu, 04 Jul 2019 14:08:26 GMT

Superb, works perfectly, if I then want to add [CalYear] as a third layer?

Re: Gauge

vmoreno2605 — Thu, 04 Jul 2019 14:24:50 GMT

you just need to add another if like this:

if(GetSelectedCount(CalDay)>0,Count(Distinct(BedDaysPatientID))/((Sum(Data)/2)*Count(CalDay)),

if(GetSelectedCount(CalMonth)>0,Count((BedDaysPatientID))/((Sum({<CalDay={1}>}Data)/2)*Count(CalDay)),

if(GetSelectedCount(CalYear)>0,/*YOUR CODE HERE*/

) ))

Re: Gauge

kev6brown — Thu, 04 Jul 2019 14:31:04 GMT

That's what I assumed but it didn't work. I'll take another look. thank you

Re: Gauge

kev6brown — Fri, 05 Jul 2019 10:02:04 GMT

So, this work brilliantly -

if(GetSelectedCount(CalDay)>0,Count ({<WardCode={'Grattan'}>}Distinct(BedDaysPatientID)),
if(GetSelectedCount(CalMonth)>0,Count({<WardCode={'Grattan'}>}(BedDaysPatientID))))

However when I try to add CalYear

if(GetSelectedCount(CalDay)>0,Count ({<WardCode={'Grattan'}>}Distinct(BedDaysPatientID)),
if(GetSelectedCount(CalMonth)>0,Count({<WardCode={'Grattan'}>}(BedDaysPatientID))),
if(GetSelectedCount(CalYear)>0,Count({<WardCode={'Grattan'}>}(BedDaysPatientID))))

It doesn't work and I get an error in expression: If takes 2-3 parameters

Re: Gauge

vmoreno2605 — Fri, 05 Jul 2019 11:55:08 GMT

Try it like this

if(GetSelectedCount(CalDay)>0,Count ({<WardCode={'Grattan'}>}Distinct(BedDaysPatientID)),
if(GetSelectedCount(CalMonth)>0,Count({<WardCode={'Grattan'}>}(BedDaysPatientID)), /*Only two ')', */
if(GetSelectedCount(CalYear)>0,Count({<WardCode={'Grattan'}>}(BedDaysPatientID)))))