topic Top 10 For Specific Items in QlikView

Top 10 For Specific Items

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

Hello:

This should be fairly simple, but I have to be missing something simple. I have a straight table where I want to show the top tem items if they equal certain values. I probably need 7 tables based on different values, but if someone can show me what I'm missing on one example, I can do the others.

Any ideas anyone? As always, thanks in advance for any and all help.

Re: Top 10 For Specific Items

sunny_talwar — Sun, 24 Sep 2017 22:33:00 GMT

It doesn't look and sound simple to me... at least without a sample... very difficult to say by just looking at a expression as to what you might be looking to get... Would you be able to share a sample?

Re: Top 10 For Specific Items

swuehl — Sun, 24 Sep 2017 22:40:10 GMT

Maybe like this as single calculated dimension:

=Aggr(

If(

Rank(

Sum(

{<project_include_in_forecast = {1},project_is_active = {1},
estimate_include_in_forecast={1},estimate_is_active ={1}, effective_item_action={'Add'} ,

catalog_item_class_tier2 = {'Microsoft SQL on PSI', 'Oracle on PSI', 'PSI Basic', 'PSI Basic High Density Disk'}

effective_item_qty)

,3,1)

<=10, project_id)

,project_id

)

Re: Top 10 For Specific Items

pnn44794 — Sun, 13 Oct 2019 01:16:43 GMT

Hello Stefan:

This did not work.

Re: Top 10 For Specific Items

pnn44794 — Sun, 24 Sep 2017 23:02:08 GMT

Good point Sunny and I should have thought of that. I will post a data sample in a couple of minutes.

Re: Top 10 For Specific Items

swuehl — Sun, 24 Sep 2017 23:05:05 GMT

Maybe remove the comma before effective_item_qty?

Re: Top 10 For Specific Items

pnn44794 — Sun, 13 Oct 2019 01:17:17 GMT

No luck with removing the comma.

Re: Top 10 For Specific Items

pnn44794 — Sun, 13 Oct 2019 01:17:54 GMT

Hello Sunny:

Ok.

Re: Top 10 For Specific Items

pnn44794 — Mon, 25 Sep 2017 00:16:14 GMT

Hello Stefan:

I did have one field wrong (triple checking the rest),where I renamed estimate_item_action to [Item Action], if that makes a difference. I did change the calculated dimension to reflect the correct name with no luck.

Re: Top 10 For Specific Items

sunny_talwar — Mon, 25 Sep 2017 00:29:22 GMT

Good start... not what is the expect output you expect to see from this data....numerical expected output

Re: Top 10 For Specific Items

pnn44794 — Sun, 13 Oct 2019 01:13:53 GMT

I expect to see something like the following screenshot without having to filter for it. Note this is filtered for 2017 Q4. I also have 2018 Q1 to Q4 data, so with no filtering, I would expect top 10 for201704 to 201804 and then top 10 by each quarter when filtered. Make sense? In other words, an overall top 10 and then a top ten by quarter once you filter for a specific quarter.

Re: Top 10 For Specific Items

sunny_talwar — Mon, 25 Sep 2017 01:17:35 GMT

Something like attached

Re: Top 10 For Specific Items

pnn44794 — Mon, 25 Sep 2017 02:02:01 GMT

This appears to do the trick. Thank you very much. I will do some additional testing tomorrow morning and see if I can replicate the code (shouldn't be a problem) for other product categories and provide an update.

The use of *= is the first time I've seen that. From what I'm reading about it, it specifies intersection or in other words give me just what matches what's in the { } for catalog_item_class_tier2 *=. Is that right?

Re: Top 10 For Specific Items

sunny_talwar — Mon, 25 Sep 2017 08:30:55 GMT

The use of *= is the first time I've seen that. From what I'm reading about it, it specifies intersection or in other words give me just what matches what's in the { } for catalog_item_class_tier2 *=. Is that right?

That is correct... I actually used it for my own testing not knowing if you needed it or not. In case you want to always show the specified catalog_item_class_tier2, then don't use the *. It is only when you need the intersection between your selection and what is specified within the set... you would need to use this....

Re: Top 10 For Specific Items

pnn44794 — Mon, 25 Sep 2017 23:52:55 GMT

Hello Sunny:

Sorry for the delay in replying. I was able to successfully test today with your solution and I was able to tweak it for use with other similar scenarios (i.e. VSI, SAN, etc). Thank you very much for your help..................again!!! You're always great to work with and patient. It's appreciated.

I'll mark your answer as correct.

On a side note, I also opened One To Many Problem - Multiple Fact Tables. Not sure if you may have any insight into this or if you have time to take a look. If not, perfectly understood. Again, thank you for your help.

Re: Top 10 For Specific Items

pnn44794 — Mon, 25 Sep 2017 23:53:58 GMT

Stefan, thank you again for your reply. It is helpful and I will mark it as such. Thanks again.