3 Replies Latest reply: Dec 22, 2017 8:53 AM by Randy Bender RSS

    KPI Box giving unpredictable conditional colors

    Randy Bender

      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

        • Re: KPI Box giving unpredictable conditional colors
          Luis Madriz

          Hi Randall,

           

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

           

          Cheers,

           

          Luis

            • Re: KPI Box giving unpredictable conditional colors
              Randy Bender

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

            • Re: KPI Box giving unpredictable conditional colors
              Satish Kurra

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