Fill Rate Status Calculation

[My_Rebecca]

Both mezzanine and pallet fill rate need to be assessed. As long as any rate is over 85%, the "Status" will become "Alert"; if any rate is over 92%, the Status will become "Overload".

How to create the column of "Status" by script or expression? I think "OR" is hard to realize in Qlik Sense.

The excel formular is IF(OR(E3>0.92,H3>0.92),"Overload",IF(OR(E3>0.85,H3>0.85),"Alert","Normal"))

Daily Fill Rate Report
Week	Date	Mezzanine	Total Mezzanine	Mezzanine Fill Rate	Pallet	Total Pallet	Pallet Fill Rate	Status
2023-WK1	1/3/2023			72.75%			90.86%	Alert
2023-WK1	1/4/2023			73.28%			91.65%	Alert
2023-WK1	1/5/2023			73.79%			93.56%	Overload
2023-WK1	1/6/2023			74.16%			90.01%	Alert
2023-WK2	1/9/2023			74.16%			90.27%	Alert
2023-WK2	1/10/2023			86.59%			77.66%	Alert
2023-WK2	1/11/2023			78.78%			77.66%	Normal
2023-WK2	1/12/2023			79.60%			90.09%	Alert

[sandeep-singh]

hi @My_Rebecca , you can try this

Test:
Load * Inline [
Mezzanine, Pallet
72.75%, 90.86%
73.28%, 91.65%
73.79%, 93.56%
74.16%, 90.01%
74.16%, 90.27%
86.59%, 77.66%
78.78%, 77.66%
79.60%, 90.09%
];

Output:
load *,
if(Mezzanine > 0.92 or Pallet > 0.92, 'Overload',
if(Mezzanine > 0.85 or Pallet > 0.85, 'Alert', 'Normal')) as Status
Resident Test;

drop Table Test;

[My_Rebecca]

Dear @sandeep-singh , if first date line is added "IN_Date", how to search the today's "Status"? My expression does not work.

=if({<[IN_Date]={"$(=Date(today()))"}>}[Fill Rate Status]='Overload',RGB(255,0,0),if({<[IN_Date]={"$(=Date(today()))"}>}[Fill Rate Status]='Alert',RGB(255,165,0),RGB(255,255,255)))

[sandeep-singh]

Hi @My_Rebecca , you can try this code on your text or background color expression. Please change the date format as per the format you are using in your script.

{<[IN_Date]={"$(=Date(today(),'MM/DD/YYYY'))"}>} pick(match(Status,'Overload','Alert')+1,'RGB(255,255,255)','RGB(255,0,0)','RGB(255,165,0)')

[My_Rebecca]

Dear @sandeep-singh , what does it meaning:

pick(match(Status,'Overload','Alert')+1

[My_Rebecca]

Dear @sandeep-singh , I used "OR" in the expression, but seems not succeed.

=if(Sum({<[IN_Date]={"$(=Date(today()))"}>}[Mezzanine1])>0.92 or Sum({<[IN_Date]={"$(=Date(today()))"}>}[Pallet1])>0.92,'RGB(255,0,0)',
if(Sum({<[IN_Date]={"$(=Date(today()))"}>}[Mezzanine1])>0.85 or Sum({<[IN_Date]={"$(=Date(today()))"}>}[Pallet1])>0.85,'RGB(255,165,0)','RGB(255,255,255)'))

[sandeep-singh]

The Pick() function allows you to return a value from a list of values based on an index. Exp Pick(index, value1,..,valueN ) where index is the index of the value you want to return, and value1 through valueN are the values to choose from.

The Match() function is used to find the position of a value within a list of values.Match(value, value1,..., valueN) where value is the value you want to find, and value1 through valueN are the values to search through.

+1 is the else statement for pick function.

[sandeep-singh]

Try this

{<[IN_Date]={"$(=Date(today(),'MM/DD/YYYY'))"}>}
if(Mezzanine > 0.92 or Pallet > 0.92, RGB(255,0,0),
if(Mezzanine > 0.85 or Pallet > 0.85, RGB(255,165,0), RGB(255,255,255)))

Please make sure that date format matches your script date format

[My_Rebecca]

Dear @sandeep-singh , please look at my case: I need to set 2 limits in this KPI. Either Mezzanine or Pallet exceeds 85%, the KPI turns to brown, either 95% is red.

But in KPI display, it may be impossible to realize 2 limits by expression. Could you please take a look? Thanks.

=Sum({<[IN_Date]={"$(=Date(today(),'M/D/YYYY'))"}>}[Mezzanine1])>0.95 or Sum({<[IN_Date]={"$(=Date(today(),'M/D/YYYY'))"}>}[Pallet1])>0.95

[sandeep-singh]

Hi @My_Rebecca, the Limit is dynamic, based on the user selection. The set analysis "{<[IN_Date]={"$(=Date(today(),'M/D/YYYY'))"}" will won't work in the limit expression. If you want, you can provide a date filter to select today's date and simply add limit as 0.85 and 0.95 since you are already taking the sum of Mezzanine or Pallet in your expression.