topic Re: Pareto Help in QlikView

Pareto Help

sunny_talwar — Thu, 24 Mar 2016 20:49:14 GMT

Hi Team -

Is it possible to do something like the below? Currently I am getting this

I would like to move Dim = Other into the Others Bucket and then show the rest 80%. Something like this:

Dim	=Sum(Value)
A	40
M	12
C	9
L	7
F	6
K	5
Others	21

I am attaching a sample with this application. Please note that this is just a small sample of large amount of data and I am looking for front end options instead of back end.

Thanks,

Sunny

Re: Pareto Help

sunny_talwar — Thu, 24 Mar 2016 20:54:55 GMT

I guess adding this as the sort expression seems to help the sample, but on the original data this is very slow.

=Match(Dim, 'A', 'M', 'C', 'L', 'F', 'K', 'D', 'J', 'P', 'X', 'Y', 'Other')

What would be the best way to push Other down the sort order?

Re: Pareto Help

sunny_talwar — Thu, 24 Mar 2016 21:00:55 GMT

So what I have above is using the names as is, but I tried a slightly complex version since the Dim names cannot be entered like this as the value will and can change. This is what I tried:

=Match(Dim, $(=Chr(39) & Concat({<Dim -= {'Other'}>}DISTINCT Dim, Chr(39) & ',' & Chr(39), -Aggr(Sum(Value), Dim)) & Chr(39)), 'Other')

It works pretty well for the sample, but looking for better ways to do this.

Thanks,

Sunny

Re: Pareto Help

swuehl — Thu, 24 Mar 2016 23:01:21 GMT

You are way too fast Sunny,

I think you should allow the audience to read and understand your request before solving it

I think the idea using a pareto based on first sorted values and then sort by yourself is pretty good.

Concerning the sort by expression on sort tab, I think this should also work and may be somewhat less complex:

=Sum({<Dim -= {Other}>} Value)

(with sort order descending)

And I think you need to disable the option 'Allow interactive sort' since this will distort your chart.

Regards,

Stefan

Re: Pareto Help

sunny_talwar — Fri, 25 Mar 2016 00:08:12 GMT

This was it. That did the job with my actual data as well. I knew no matter what, I can expect to get a good solid idea from you.

Thanks for your help Stefan.