Solved: Set Analysis with Rank - Qlik Community

Report Inappropriate Content · ‎2012-10-04

Hi,

I have a straight table and I only want to display the top 20 and Buying Groups and the Total of those Top 20 Buying Groups. If I create a set analysis expression and limit the presentation of the table to the first 20 the total at the bottom does not work. To fix this I thought I could use the Rank function to get the MTD and YTD figures. For some reason I just can't get the expression to work for YTD and I am not sure 100% if MTD is right either.

Could someone please point me in the right direction?

sum({<Year={"2012"}, Month={">=4<=9"},[Buying Group]={"=rank(sum([Exclusive Amount]))<=20"}>} [Exclusive Amount])

Cheers

John

swuehl · ‎2012-10-19

John,

it seems that you want to compare the current Month Value for the TOP20 of that Month, but the YTD Value for the Top 20 of previous 6 month (Apr to Sep). As said, these are two different sets of Buying Group values.

But for sure you can show them in the same table graph, it seems that this is what you were after all the time. The numbers should now be correct.

Just change in YTD expression the lower date limit in the set modifier for the sum in the rank:

=Sum({<[Buying Group]={"=rank(sum({<Date={'>=$(=Date(monthstart(max(Date),-5)))<=$(=Date(max(Date)))'}, Year, Month, [Month Year]>}[Exclusive Amount]))<21"}, Year=,Month=, [Month Year]=,

Date={">=$(=Date(monthstart(max(Date),-5)))<=$(=Date(max(Date)))"}

>} [Exclusive Amount])

If this is not what you want, I am at the end with my ideas. Maybe someone else can jump in.

Have a nice weekend,

Stefan

View solution in original post

shree909 · ‎2012-10-05

HI try this

=rank(sum({<Year={"2012"}, Month={">=4<=9"},[Buying Group]={"[Exclusive Amount]'}>} [Exclusive Amount]) <=20

Report Inappropriate Content · ‎2012-10-05

Hi John,

If i understand your requirement correct, you need to show top 20 values and the total of the 20 values in the StraightTable.

To acheive this you can use the dimension limits.

Select --> dimension limits and set the Limits to Larget 20 only.

Go to Expression Tab, sleect the expression and in the "Total Mode" option, change it from Expression default to "sum" of rows explicitly.

Regards

Satheesh

Report Inappropriate Content · ‎2012-10-05

Hi Guys,

Thanks for your answers, I tried both but they haven't worked. I have included a dashboard with dummy data so it may help. If you highlight Apr - Sep the total on the table is correct, using the expression for YTD results in the wrong number, i think month will be the same.

Shree909 - I tried your expression but had to modify it slightly, it hasn't worked however.

Satheesh - I am not using version 11, so don't have that option, I will install the latest version though and give it a go.

I am intrigued however to figure this expression out.

Thanks

John

Report Inappropriate Content · ‎2012-10-05

Hi,

I installed QV11 but it doesn't work.

I have included an updated dashboard with sample data.

Its weird!

Cheers

John

Report Inappropriate Content · ‎2012-10-06

Hi John,

Dimension Limits restrict the values based on first expression in the chart.In your sample, since Current Month is being the first epression, the top 20 buying group for the current month has been selected and the sum(YTD) for those buying groups is 353018. However if you want to select the top 20 Buing groups based on the YTD then you have to move the YTD exp as the first one in the table and this will give you the sum(YTD) as 491307 as you are expecting it to be

Regards

Satheesh

jagan · ‎2012-10-06

Hi Satheesh,

Hope attached file helps you.

Regards,

Jagan.

Report Inappropriate Content · ‎2012-10-16

Hi Guys,

Thanks for all your replies.

Satheesh - What you said is correct, as I moved the expression to be first it worked using the limited dimensions

Jagan - Your example was very helpful thank you, I am able now to get current month spot on using rank, however what seems to happen now is my YTD on the same chart does not sum up correctly. I have included the same dashboard to show it.

I can't seem to get a YTD alongside my current month.

Any suggestions

Cheers

John

swuehl · ‎2012-10-16

Try this as calculated dimension in your table chart at the right:

=aggr(

if(rank(sum({$<Year={"2012"}, Month={">=4<=9"}>}[Exclusive Amount]))<21, only({1}[Buying Group]) )

,[Buying Group])

Report Inappropriate Content · ‎2012-10-16

Hi

Cheers, that gave the correct answer for YTD, but now the Curr. Month Column is out.

Man this is probably the worst and annoying straight table issue I have had.

You would think if you want the top 20 curr and YTD QlikView could do this as standard!!!!

Thanks for your help

Attached is the doc with your expression.

Cheers

John

Set Analysis with Rank

Related Topics