Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Mauritz_SA
Contributor III

Distribution plot help

Hi all

I really hope that someone can help - it is driving me crazy! I have a data set with a lot of data which can be simplified to something like the table below:

ClientInspectionScore
Client_AHealth1
Client_BHealth9
Client_CHealth7
Client_ASafety1
Client_BSafety8
Client_CSafety1
Client_ASecurity5
Client_BSecurity2
Client_CSecurity7
Client_AEnvironment2
Client_BEnvironment6
Client_CEnvironment7

 

I would like to show the Min and Max for each Inspection (Health, Safety, Security and Environment - disregarding any selections) and then I also want to show the average for the clients selected as another dot on a distribution plot. I also need the bands to be shown. Something like the graph below (it is the wrong data, but it is just to give you an idea):

Distribution Plot I want.PNGDesired layout with grey bands.

I tried making Inspection my Y-axis, ValueList('Min','Max','Avg') my Points and my X-axis (measure) the following:

If(ValueList('Min','Max','Avg')='Min',
Min({1}Score),
If(ValueList('Min','Max','Avg')='Max',
Max({1}Score),
If(ValueList('Min','Max','Avg')='Avg',
Avg(Score)
)
)
)

It gives me the correct data, but the bands disappear like in the screenshot below:

Distribution Plot I get.PNGDistribution plot I get.

Like I said, the data is fine, but the layout not as I don't get the bands anymore (which is an explicit requirement). I have spent hours on this and really hope there is a simple answer. I cannot show all the data points as my actual data set will have way more than 100 scores for each inspection. I have attached* the sample app.

Any help would be greatly appreciated (other suggestions also welcome, but I am not allowed to use a Box Plot).

Regards,

Mauritz

PS. I have tried doing this with a stacked combo chart and tried to make the bottom bar transparent, but I cannot use ARGB colours as part of my master measure colours and the white doesn't look goodSmiley Sad.

 

1 Solution

Accepted Solutions
Highlighted
Employee
Employee

Re: Distribution plot help

Hi Mauritz,

There seems to be something with the value list. Not really sure what, but I've asked the devs to have a check.

A workaround that works for me is to create a new 'Temp' field in a data island with the values Max, Min, Avg.

Then I changed the measure to be just

If(Temp='Min',
Min(Score),
If(Temp='Max',
Max(Score),
Avg(Score)
)
)

Seems to work

 

Dist.PNG

Regards,

Patrik.

4 Replies
Highlighted
Employee
Employee

Re: Distribution plot help

Hi Mauritz,

There seems to be something with the value list. Not really sure what, but I've asked the devs to have a check.

A workaround that works for me is to create a new 'Temp' field in a data island with the values Max, Min, Avg.

Then I changed the measure to be just

If(Temp='Min',
Min(Score),
If(Temp='Max',
Max(Score),
Avg(Score)
)
)

Seems to work

 

Dist.PNG

Regards,

Patrik.

Mauritz_SA
Contributor III

Re: Distribution plot help

Hi Patrik

Thank you so much for the feedback. Your solution works perfectly! Would be nice to know if this can be done from the front end using something like Valuelist as other users won't have access to the data load.

Thanks again.

Regards,

Mauritz

Employee
Employee

Re: Distribution plot help

Hi Maurtiz,

The issue is indeed with the valuelist. When the chart it tries to get the max and min value of each row to calculate the background box. The expression to find the max value is in your case

Max( total <[Inspection]> Aggr( If(ValueList('Min','Max','Avg')='Min',
Min({1}Score),
If(ValueList('Min','Max','Avg')='Max',
Max({1}Score),
If(ValueList('Min','Max','Avg')='Avg',
Avg(Score)
)
)
), [Inspection], [=ValueList('Min','Max','Avg')] ), 1 )

The problem here is that the aggr function doesn't accept the valuelist. Therefore the max value can't be calculated. 

From documentation

Limitations:  

Each dimension in an Aggr() function must be a single field, and cannot be an expression (calculated dimension).

https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/aggr....

 

So nothing we can easily solve in the visualization for now.

Regards,

Patrik. 

 

 

 

 

Mauritz_SA
Contributor III

Re: Distribution plot help

Thanks Patrik. I really appreciate the help and insight!