topic Re: Set Analysis with Rank in QlikView

Set Analysis with Rank

Fri, 05 Oct 2012 03:12:29 GMT

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

Re: Set Analysis with Rank

shree909 — Fri, 05 Oct 2012 04:38:05 GMT

HI try this

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

Re: Set Analysis with Rank

Fri, 05 Oct 2012 06:20:37 GMT

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

Re: Set Analysis with Rank

Fri, 05 Oct 2012 22:17:24 GMT

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

Re: Set Analysis with Rank

Fri, 05 Oct 2012 22:59:20 GMT

Hi,

I installed QV11 but it doesn't work.

I have included an updated dashboard with sample data.

Its weird!

Cheers

John

Re: Set Analysis with Rank

Sat, 06 Oct 2012 06:42:15 GMT

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

Re: Set Analysis with Rank

jagan — Sat, 06 Oct 2012 14:50:28 GMT

Hi Satheesh,

Hope attached file helps you.

Regards,

Jagan.

Re: Set Analysis with Rank

Tue, 16 Oct 2012 22:08:32 GMT

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

Re: Set Analysis with Rank

swuehl — Tue, 16 Oct 2012 22:28:15 GMT

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])

Re: Set Analysis with Rank

Wed, 17 Oct 2012 01:20:14 GMT

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

Re: Set Analysis with Rank

swuehl — Wed, 17 Oct 2012 09:39:14 GMT

John,

maybe I am a bit confused what you want to see.

I assume you want to the the top 20 Buying Group by sum(Exclusive Amount) for the selected period (1 Month).

Then you want to display this sum(Exclusive Amount), and the accumulated sum(Exclusive Amount) for the period starting 5 months prior the selected month and including the end of the selected month.

I think you should be fine by just using [Buying Group] as dimension.

Then one expression to see the sum(Exclusive Amount) for the selected month, only for the top 20 in this selected month:

=Sum({<[Buying Group]={"=rank(sum([Exclusive Amount]))<21"}>}[Exclusive Amount])

and one expression for the top 20, but for the last 6 months:

=Sum(

{<[Buying Group]={"=rank(sum([Exclusive Amount]))<21"},

Year=, Month= ,Date={"$(='>='&addmonths(MonthsStart(1,date(date#(Only([Month Year]),'MMM-YYYY'))),-5)&'<='&MonthsEnd(1,date(date#(Only([Month Year]),'MMM-YYYY'))))"}>} [Exclusive Amount])

Note that I am clearing now the selections in the other calendar fields Year and Month (and you should potentially add all calendar fields here that a user might do selections in, that may interfere with the set modifier for Date).

I think the set modifier for Date can be slightly simplified, for example if you use a Month Year field that holds a numerical value for the monthstart itself (like QV created value using MonthName() ).

Also note that this expression breaks if the user selects more than one Month Year.

If this is not what you want, I think I would need a clarification of your requirements first.

Regards,

Stefan

Re: Set Analysis with Rank

Wed, 17 Oct 2012 20:43:34 GMT

Hi,

Thanks for your reply.

You right in my requirements, I am looking for Curr Month ($79,048) and YTD(APR-SEP) $491,306.

When I use Buying Group and your suggested expressions you can see the Curr Month is correct, but YTD is incorrect, this has been the problem from the start.

The last post by swuehl worked for YTD but made the Curr Month total incorrect, I can't seem to get BOTH columns showing the correct values.

I have included the sample dashboard with a table using your expressions, you can see Curr Month is correct, YTD is incorrect. swuehl table YTD is correct, Curr Month is incorrect.

Cheers

John

Re: Set Analysis with Rank

swuehl — Wed, 17 Oct 2012 20:52:09 GMT

If I manually add up sum(Exclusive Amount) for the months Apr to Sep 2012 for the same group that makes top20 in Sep, I do get 353,018, not 491,306, so it matches my YTD expression.

Please explain how you get to your higher number.

Re: Set Analysis with Rank

Wed, 17 Oct 2012 23:31:28 GMT

Hi,

When I manually highlight the months at the top Apr-Sep the amount is $491,306. Select just Sep the Curr Month is correct, however YTD now is $353,018. You would expect it to be $491,306 since the expression should be doing the same thing as highlighting Apr-Sep manually.

Hope that helps

Cheers

John

Re: Set Analysis with Rank

swuehl — Thu, 18 Oct 2012 09:28:54 GMT

Hi John,

if you compare the totals like that, I think you are comparing apples with oranges, since you are changing the members of the buying group that makes up TOP 20 in the current application, because the TOP20 is calculated for the selected period.

I assume you want the TOP 20 group for the highest Year Month selected then (i.e. Sep 2012), but you need to tell QV this, maybe like:

=Sum({<

[Buying Group]={"=rank(sum({<Date={'>=$(=Date(monthstart(max(Date))))<=$(=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])

and

=Sum({<

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

>}[Exclusive Amount])

Check also attached.

Regards,

Stefan

Re: Set Analysis with Rank

Thu, 18 Oct 2012 20:58:28 GMT

Hi Stefan,

Cheers for post, the YTD total still calculates to $353,017 with your expression. You right I do want the top 20 Buying Group for selected period, however the correct YTD total Apr-Sep is $491,307, and the correct Curr Month should be $79,048, good thing is Curr Month is correct.

I understand Curr Month ranking will be Buying Group will be different separately, however if I benchmark top 20 on YTD all i would like to do is show the Curr Month value for that Buy Group.

I really do appreaciate your help on this, perhaps I am just not understanding something, but from my experience with set analysis all I am trying to do is replicate the filter process I am doing manually.

Cheers

John

Re: Set Analysis with Rank

swuehl — Fri, 19 Oct 2012 17:48:12 GMT

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

Re: Set Analysis with Rank

Wed, 24 Oct 2012 02:23:13 GMT

Hi Swuehl,

Man its correct thank you so much, what a crazy expression it has turned out to be. The logic is not how I imagined it, but makes sense now I see it.

Once again thank you for your continued support.

Regards,

John