Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
randall_bender
Contributor
Contributor

KPI Box giving unpredictable conditional colors

Hi All,

I have six KPI boxes on a screen that I display as green, yellow, or red. Both the KPI expressions and the conditional color expressions are all calculated formulas using variables from an external file.

The issue I am having is that sometimes (it appears to be random) when I filter the data, the boxes switch to the wrong colors (red when they should be green). 95% of the time it works as expected. Has anybody encountered this issue? I am working on Qlik Sense Enterprise version 3.1 SR5.

Thanks in advance for any help you can provide!

Randy

3 Replies
luismadriz
Specialist
Specialist

Hi Randall,

Can you share the KPI expression? if you have limits as expressions please share them too,

Cheers,

Luis

satishkurra
Specialist II
Specialist II

Question is too generic. Please provide the logic or supporting APP

randall_bender
Contributor
Contributor
Author

Hi Luis,

Thanks for the response. The expressions are not failing and they are always returning the exact same numbers. That's what is odd...there is no inconsistency in the data. I can select the same filter 100 times, and 95 times the color expression works as expected. 5 times it will not, even though the conditional color expressions are returning exactly the same result. I am 100% certain of this because I also have the color expression values in separate KPI boxes (they are thresholds displayed to the end user so they know when they are approaching yellow and red). There is no data-driven reason for this behavior, so I am trying to find out if anyone has experienced this issue. My guess is that there is a calculation timing issue on the color expressions since there is some complexity to them, but it's hard to be sure.

Nonetheless, I have pasted below the variables that comprise one of the KPIs, the definitions of the variables and sub-variables within them, and the variables and sub-variables for the two conditional color expressions (some are repeated, but I included them more than once to keep the variable components together). You'll have to apply some logic to understand how the nested variables come together to form a full expression, but all the information is there.

Thanks for your help!

Randy

-----------------------------------------------------------------------------------------------------------------------------------

//KPI expression

If($(mCAUTIObservedNHSNCountDateParameter($(msCurrentFYTDNHSNCAUTI)))>0,

   If(YearEnd(Max({<[HAI Updated Metric]={'CAUTI'}>}[HAI Updated Through]),0,vcgFiscalYearBegin)=YearEnd($(vAsOfDate),0,vcgFiscalYearBegin),

  $(mCAUTIObservedNHSNSumDateParameter($(msCurrentFYTDNHSNCAUTI))),

     Null()

    ),

   Null()

  )

//Variable defintions for first If statement

mCAUTIObservedNHSNCountDateParameter = Count({<$1,Month=,Week=,Weekday=Year=,[Month Year]=,Quarter=>}[NHSN CAUTI Number Observed NHSN])

msCurrentFYTDNHSNCAUTI = $(msFYTDNHSNCAUTI(0,vcgFiscalYearBegin))

msFYTDNHSNCAUTI = [Date]={">=$(=yearstart(addyears($(vAsOfDateNHSNCAUTI), $1), 0, $2))<=$(=addyears($(vAsOfDateNHSNCAUTI),$1))"}

vAsOfDateNHSNCAUTI = Max({<[HAI Updated Metric]={'CAUTI'}>}[HAI Updated Through])

vcgFiscalYearBegin = 10

//Variable definitions for second If statement

vcgFiscalYearBegin = 10

vAsOfDate = Date(max([Date])) 

//Variable definitions for KPI calculation

mCAUTIObservedNHSNSumDateParameter = Sum({<$1,Month=,Week=,Weekday=Year=,[Month Year]=,Quarter=>}[NHSN CAUTI Number Observed NHSN])

msCurrentFYTDNHSNCAUTI = $(msFYTDNHSNCAUTI(0,vcgFiscalYearBegin))

msFYTDNHSNCAUTI = [Date]={">=$(=yearstart(addyears($(vAsOfDateNHSNCAUTI), $1), 0, $2))<=$(=addyears($(vAsOfDateNHSNCAUTI),$1))"}

vAsOfDateNHSNCAUTI = Max({<[HAI Updated Metric]={'CAUTI'}>}[HAI Updated Through])

vcgFiscalYearBegin = 10

//Expression for first conditional color threshold

$(mPatientImpactCAUTIExceedsDateParameter($(msCurrentMonth)))+1

//Variable defintions for first conditional color threshold

mPatientImpactCAUTIExceedsDateParameter = Sum({<$1,Month=,Week=,Weekday=Year=,[Month Year]=,Quarter=>}[Patient Impact CAUTI Exceeds])

msCurrentMonth = $(msMonth(0))

msMonth = [Date]={">=$(=monthstart(addmonths($(vAsOfDate), $1)))<=$(=monthend(addmonths($(vAsOfDate),$1)))"}

vAsOfDate = Date(max([Date]))

//Expression for second conditional color threshold

$(mPatientImpactCAUTIMeetsDateParameter($(msCurrentMonth)))+1

//Variable defintions for second conditional color threshold

mPatientImpactCAUTIMeetsDateParameter = Sum({<$1,Month=,Week=,Weekday=Year=,[Month Year]=,Quarter=>}[Patient Impact CAUTI Meets])

msCurrentMonth = $(msMonth(0))

msMonth = [Date]={">=$(=monthstart(addmonths($(vAsOfDate), $1)))<=$(=monthend(addmonths($(vAsOfDate),$1)))"}

vAsOfDate = Date(max([Date]))