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: 
Aj_
Contributor
Contributor

Disregarding chart dimension (x axis)

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:

 

StatusRecordDate GroupID Remarks (for explanation purpose) - not a data
Green2/1/20RECD001 
Amber3/1/20RECD001 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)
Green4/1/20RECD001 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)
Green3/1/20RECD002 
Amber4/1/20RECD002for this RECD002, Green status  should not be considered (unless date selected is 3/1/20)
Amber3/1/20RECD003 
Amber3/1/20RECD004 
Green2/1/20RECD005 
Red4/1/20RECD005When 4/1/20 or 2/1/20 is selected, RECD005 should be only counted against Red (and not Green)
Amber2/1/20RECD006 
Green3/1/20RECD006When 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):

 

Barchart_example.PNG

 

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!

1 Solution

Accepted Solutions
edwin
Master II
Master II

here is a simpler alternative to padding

View solution in original post

6 Replies
edwin
Master II
Master II

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.

edwin
Master II
Master II

this shows the bar chart with no suppression on Zero and missing

edwin
Master II
Master II

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

edwin_0-1614482902434.png

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)

edwin
Master II
Master II

here is a simpler alternative to padding

Aj_
Contributor
Contributor
Author

Thanks a lot Edwin! This solution seems to have done the trick 🙂 

edwin
Master II
Master II

yw