topic Re: Help Using Rank Function in QlikView

Help Using Rank Function

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

Hi,

Can someone help me using Rank function, i have this example but the output is not what i expected :

=aggr(if(rank(

Sum({<

DataType = {'Outsort'}

, YearMonth = {$(=Max(YearMonth))}

, ItemType = {'12'}

, Range = {'XXX'}

>} [Scrapped quantity])

Sum({<

DataType = {'Outsort'}

, YearMonth = {$(=Max(YearMonth))}

, ItemType = {'12'}

, Range = {'XXX'}

>} [Sent to Repair quantity])

,4)=1,

Sum({<

DataType = {'Outsort'}

, YearMonth = {$(=Max(YearMonth))}

, ItemType = {'12'}

, Range = {'XXX'}

>} [Scrapped quantity])

Sum({<

DataType = {'Outsort'}

, YearMonth = {$(=Max(YearMonth))}

, ItemType = {'12'}

, Range = {'XXX}

>} [Sent to Repair quantity])),[Scrap description])

Using this expression i intend to get the quantities of every item with Scrap Description Rank 1, and it actually returns the sum of the total quantity :

But what i want is to list them by item like this :

Anyone knows how to solve this ?

Re: Help Using Rank Function

sunny_talwar — Thu, 19 Jul 2018 16:25:42 GMT

Would you be able to share a sample to look and play around with?

Re: Help Using Rank Function

eduardomendes — Fri, 20 Jul 2018 08:40:26 GMT

Hi There,

This is part of a company report and i'me not allowed to share it, that's the reason of the black square hidding part of information. I've been told that using the rank function would be possible to reach the expected results but no luck yet ...

Have any idea on what way to go ?

Re: Help Using Rank Function

marcus_sommer — Fri, 20 Jul 2018 15:05:25 GMT

It looked that you missed the [Item Number] within your calculated dimension and it might be look rather like:

=aggr(if(rank(

Sum({<

DataType = {'Outsort'}

, YearMonth = {$(=Max(YearMonth))}

, ItemType = {'12'}

, Range = {'XXX'}

>} rangesum([Scrapped quantity], [Sent to Repair quantity]))

,4)=1,

[Item Number] & '-' &

Sum({<

DataType = {'Outsort'}

, YearMonth = {$(=Max(YearMonth))}

, ItemType = {'12'}

, Range = {'XXX'}

>} rangesum([Scrapped quantity], [Sent to Repair quantity]))

,[Scrap description])

whereby I'm not sure that the grey part makes much sense - ot should it be a different expression?

- Marcus

Re: Help Using Rank Function

sunny_talwar — Fri, 20 Jul 2018 15:15:38 GMT

You can anonymize your data.. look here

Preparing examples for Upload - Reduction and Data Scrambling

Re: Help Using Rank Function

eduardomendes — Fri, 20 Jul 2018 15:30:42 GMT

Hi Marcus,

Thanks for your reply,

I tried like you said:

=aggr(rank(

Sum({<

DataType = {'Outsort'}

, YearMonth = {$(=Max(YearMonth))}

, ItemType = {'12'}

, Range = {'Kitchen Fronts'}

>} rangesum([Scrapped quantity], [Sent to Repair quantity]))

,4)=1,[Item number] &'-'& [Scrap description])

But it returns "0",

did i miss something?

Re: Help Using Rank Function

eduardomendes — Fri, 20 Jul 2018 15:48:48 GMT

Hi Sunny,

Thanks for the info, i actually didn't know about that, but, even for doing it i will have to check if i have permission to do it.

If the report was something plain and simple i would create a similar thing to upload but the problem is that the report is very complex and to send you an example it most contain sensitive data which is "Hardcoded" in some parts of the script and won't show nothing after perform scramble.

I'll try to find a way of share something similar

But anyway thanks again for the info about data anonymize ...

Re: Help Using Rank Function

sunny_talwar — Fri, 20 Jul 2018 18:23:17 GMT

Sounds good, sorry not able to offer much help...

Re: Help Using Rank Function

marcus_sommer — Sat, 21 Jul 2018 10:03:23 GMT

It's not really clear to me what do you want to do?

Your expression should serve as a calculated dimension or as an expression to a dimension? Does the inside-aggregation with the set analysis condition itself work and returning the expected results?

- Marcus

Re: Help Using Rank Function

eduardomendes — Mon, 06 Aug 2018 11:12:40 GMT

Hi there, stalwar1‌ marcus_sommer‌

Sorry about the late reply ...

I created a similiar example of what i need:

The first chart will show for that specific day the top Defects per QTY, and i need that according to that the 3 other charts automatically show the top 2 ItemNr For each of the top Defects (1st, 2nd and 3rd). I thought that using expression with rank would be the solution but now im not so sure.

The qvw file is attached.

- Eduardo

Re: Help Using Rank Function

sunny_talwar — Mon, 06 Aug 2018 11:18:30 GMT

Are we trying to fix the bottom three charts on the top chart?

Re: Help Using Rank Function

eduardomendes — Mon, 06 Aug 2018 11:21:05 GMT

Yes, thats the point, Link the bottom charts to show detailed ItemNr Information for each of the defects visible on the Top Chart

Re: Help Using Rank Function

sunny_talwar — Mon, 06 Aug 2018 11:24:17 GMT

But isn't that what we are currently seeing? You want this to be dynamic? Meaning if you had 10 different defects on the top chart... you would want to see 10 charts below with top 2 items under each defect?

Re: Help Using Rank Function

eduardomendes — Mon, 06 Aug 2018 11:27:52 GMT

Yes, i want it to be dynamic, in the case the Top Chart will always show for example the top 5 defects for the day, but i have a lot of possible defects so the top 5 will probably change everyday, what i need is that the bottom charts always show the information according the top chart

Re: Help Using Rank Function

sunny_talwar — Mon, 06 Aug 2018 11:34:36 GMT

So, in your sample... if top chart shows top 2 defects (Broken and Fail)... we would want two charts at the bottom to do the same, right? (let's remove the third chart)

For bottom chart 1... expression

Sum({<Date = {'20180806'}, Defect = {"=Rank(Sum({<Date = {'20180806'}>}QTY)) = 1"}>}QTY)

For bottom chart 2... expression

Sum({<Date = {'20180806'}, Defect = {"=Rank(Sum({<Date = {'20180806'}>}QTY)) = 2"}>}QTY)

You can do the same thing for the next 3 charts....

Re: Help Using Rank Function

eduardomendes — Wed, 08 Aug 2018 08:10:31 GMT

Thanks stalwar1,

This works like a charm ...

Had to modify a little for the original report (mostly in the set analisys for the rank sum) but just perfect ...

Thanks