topic Sorting data based on 2 expressions (in a chart) in QlikView

Sorting data based on 2 expressions (in a chart)

isciberras — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I currently have a set of data like this:

Value	Journal ID	User
100	1	isaac
200	4	isaac
250	3	isaac
50	2	joseph
10	5	joseph
13	8	luke
12	9	mary
50	10	miranda

My aim is to create a chart with:

1 dimension (User)

and 2 expressions (sum of value) and count(distinct Journal ID) for each user.

I manage to create a chart just fine ( an example of this is attached) . However i'm having difficulty sorting the cart, I would like to sort the chart based on High value and low volume.

Does anyone know If I can achieve this?

Thanks

Re: Sorting data based on 2 expressions (in a chart)

lironbaram — Fri, 17 Aug 2018 11:43:20 GMT

can you be more specific , how can you combine the two measures

my only thought is you can use rank for both measures and take sort according to the sum of the two ranks

for example rank(sum(value))+rank(-count(distinct Journal ID) )

this way you get low ranks if you have high value low volume

Re: Sorting data based on 2 expressions (in a chart)

vishweshwarisun — Fri, 17 Aug 2018 11:54:23 GMT

do you want some thing like this

Re: Sorting data based on 2 expressions (in a chart)

PrashantSangle — Fri, 17 Aug 2018 12:13:46 GMT

what is your expected sorted value????

Regards,

Re: Sorting data based on 2 expressions (in a chart)

isciberras — Fri, 17 Aug 2018 12:35:23 GMT

Hi Vishweshwari,

Yes please that's exactly what I am trying to achieve

Re: Sorting data based on 2 expressions (in a chart)

PrashantSangle — Fri, 17 Aug 2018 12:38:19 GMT

In that case you can sort it like property > sort > sort by Y-Value - > Descending.

Regards,

Re: Sorting data based on 2 expressions (in a chart)

vishweshwarisun — Fri, 17 Aug 2018 12:39:32 GMT

kindly find the attachment.

Re: Sorting data based on 2 expressions (in a chart)

BalaBhaskar_Qlik — Fri, 17 Aug 2018 13:16:45 GMT

May be this:

Sum(Aggr(Count(Distinct JournalID),User,Value))

Re: Sorting data based on 2 expressions (in a chart)

isciberras — Fri, 17 Aug 2018 13:25:04 GMT

Thank you Vishweshwari,

Did you just sort by state and expression sum(value)?

I can see how it works perfectly on the dashboard you kindly provided but doesn't work on my dashboard. I don't know if i'm missing something or if I need to re-load my data and force the Journal ID to be numeric

Thanks,

Isaac