Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlik experts
Need help on one use case.
Requirement: A simple bar chart with 1 dimension and 1 measure
Bar chart X axis (Dimension) is column : Status
(Having 3 values: Green ,Red ,Amber)
Y axis (Measure) : Distinct count of GroupID
However the measure must be such that, when GroupID has more than 1 status available, only the highest available Status is counted for.
(Red > Amber > Green)
1 Filter object on the sheet : RecordDate
Example data:
Status | RecordDate | GroupID | Remarks (for explanation purpose) - not a data |
Green | 2/1/20 | RECD001 | |
Amber | 3/1/20 | RECD001 | only this Status must be considered for RECD001 (unless RecordDate selected is 2/1/20. In that case OK to have 1 Green status counted for) |
Green | 4/1/20 | RECD001 | this Status must be not considered for RECD001 since the Amber Status being higher. (unless RecordDate selected is 2/1/20. In that case OK to have 1 Green status counted for) |
Green | 3/1/20 | RECD002 | |
Amber | 4/1/20 | RECD002 | for this RECD002, Green status should not be considered (unless date selected is 3/1/20) |
Amber | 3/1/20 | RECD003 | |
Amber | 3/1/20 | RECD004 | |
Green | 2/1/20 | RECD005 | |
Red | 4/1/20 | RECD005 | When 4/1/20 or 2/1/20 is selected, RECD005 should be only counted against Red (and not Green) |
Amber | 2/1/20 | RECD006 | |
Green | 3/1/20 | RECD006 | When 3/1/20 or 2/1/20 is selected, RECD006 should be only counted against Amber (and not Green) |
expected bar chart output (when no record date is selected):
So far I have tried FirstSortedValue, AGGR along with TOTAL qualifier and even Only function. It feels I am close but not yet fully there!
Thanks in advance!
here is a simpler alternative to padding
to get the appropriate status per group ID you aggregate by group ID.
when you aggregate you get a temp table group ID + the expression. the if statement associates the status dimension with the max string (or aggregation expression) so when you count distinct of the resulting status and group id you get the desired result. i added 3/1/2020 Green for RECD001 just to test it when you select that date.
this shows the bar chart with no suppression on Zero and missing
there will be cases when you select a date, a specific Status will not exist, if you want all status shown in the x axis when a date is selected, it becomes a bit complicated.
if a status does not exist for the date you selected, that status will not show in the x Axis, when no date is selected then green is shown because there is an instance of green for the possible values of DATE
however, in your original example, when 3/1/2020 is selected, there will be no possible selection for Red, so you don't see red.
to remedy this, you can pad your data with Status/date combination with no measures. so the association exists for all statuses and for each date selection. this way you are able to show all statuses in the x axis. of course you have to adjust your expression to not take these padded data into account when doing the max string and if condition.
see attached (yes it does get a little complicated)
here is a simpler alternative to padding
Thanks a lot Edwin! This solution seems to have done the trick 🙂
yw