topic Re: Disregarding chart dimension (x axis) in App Development

Disregarding chart dimension (x axis)

Aj_ — Sat, 16 Nov 2024 17:26:27 GMT

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!

Re: Disregarding chart dimension (x axis)

edwin — Sun, 28 Feb 2021 03:18:03 GMT

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.

Re: Disregarding chart dimension (x axis)

edwin — Sun, 28 Feb 2021 03:20:06 GMT

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

Re: Disregarding chart dimension (x axis)

edwin — Sun, 28 Feb 2021 03:35:09 GMT

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)

Disregarding chart dimension (x axis)

edwin — Sun, 28 Feb 2021 13:18:42 GMT

here is a simpler alternative to padding

Re: Disregarding chart dimension (x axis)

Aj_ — Wed, 03 Mar 2021 15:44:13 GMT

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

Re: Disregarding chart dimension (x axis)

edwin — Wed, 03 Mar 2021 19:36:06 GMT