topic Re: Filter data in qliksense chart in App Development

Filter data in qliksense chart

Aspiring_Developer — Thu, 21 Jul 2022 13:03:44 GMT

Hello All,

I have one dimension as Brand coming from backend table having below values :-

Now , I have to show market share for these brands in a bar chart and I was facing some coloring issue so I created dummy dimension using value list and create bar chart using below expression :-

Dimension -=valuelist('BT','Plusnet','EE')

Measure=

if(valuelist('BT','Plusnet','EE')='BT',
(Sum({<[Contract Length]={'12-months'},Brand={'BT'}>}[Contract Value])/Sum({<[Contract Length]={'12-months'},Brand={'Total Market'}>}[Contract Value]))
,

if(valuelist('BT','Plusnet','EE')='Plusnet',
(Sum({<[Contract Length]={'12-months'},Brand={'Plusnet'}>}[Contract Value])/Sum({<[Contract Length]={'12-months'},Brand={'Total Market'}>}[Contract Value]))

,if(valuelist('BT','Plusnet','EE')='EE',
(Sum({<[Contract Length]={'12-months'},Brand={'EE'}>}[Contract Value])/Sum({<[Contract Length]={'12-months'},Brand={'Total Market'}>}[Contract Value]))
)))

I am able to create chart and get appropriate colours using below :-

=Pick(match(
valuelist('BT','Plusnet','EE'),
'BT','Plusnet','EE'),
RGB(85,20,179),RGB(200,30,110),RGB(52,161,160))

But now the problem is I am unable t filter this data on the above Brand filter because the in chart I have used dummy dimension.

So, is there any other way to do it in which I can filter out the data as well ?

Also, the original brand dimension has 5 brand and I have to show the data only for 3 brands in my chart i.e BT , Plusnet, EE.

Can anyone please guide me with the right way to do it ?

Thanks in advance

Re: Filter data in qliksense chart

hic — Thu, 21 Jul 2022 13:24:06 GMT

I would do it without ValueList(). Try Brand as dimension and the following as measure:

Sum({<[Contract Length]={'12-months'},Brand-={'Total Market'}>}[Contract Value])
/
Sum({<[Contract Length]={'12-months'},Brand={'Total Market'}>} total [Contract Value]))

And the following as color:

Pick(match(
Brand,'BT','Plusnet','EE'),
RGB(85,20,179),RGB(200,30,110),RGB(52,161,160))

Re: Filter data in qliksense chart

Aspiring_Developer — Fri, 22 Jul 2022 09:23:37 GMT

Hi @hic

Thank you for your response. I followed your approach and I am able to do the selections. However, it is giving me issue at an overall level.

I created the stack bar chart like below :-

It is giving me 1% for all because my numerator and denominator is showing same values :-

Sum({<[Contract Length]={'12-months'},Brand-={'Total Market'}>}[Contract Value])
/
Sum({<[Contract Length]={'12-months'},Brand={'Total Market'}>} total [Contract Value])

But when I took them seperately in KPI it is giving me different values:-

But when I make any selection on a particular month it is giving me correct result like below :-

Not sure, why it is impacting at an overall level. Can you please help ?

Thanks in advance

Re: Filter data in qliksense chart

Aspiring_Developer — Fri, 22 Jul 2022 10:31:34 GMT

Hello @hic

I also checked that when I am taking total in denominator it is giving me this value without month filter:-

And when we apply month filter it changes the total and start giving correct percentage :

So, I tried to use aggreagtion , but it didn't worked:-

Sum(total aggr(sum([Contract Value]), Brand))

Thanks

Re: Filter data in qliksense chart

hic — Fri, 22 Jul 2022 11:09:44 GMT

The following will work fine if you have Brand only as dimension:

Sum({<[Contract Length]={'12-months'},Brand-={'Total Market'}>}[Contract Value]) /
Sum({<[Contract Length]={'12-months'},Brand={'Total Market'}>} total [Contract Value])

It will then divide by the total value in the chart. But if you add a dimension, you don't want to divide by the total of the chart - you want to divide by the total of the month. So, you need to change the denominator:

Sum({<[Contract Length]={'12-months'},Brand-={'Total Market'}>}[Contract Value]) /
Sum({<[Contract Length]={'12-months'},Brand={'Total Market'}>} total <MonthName> [Contract Value])