Set Analysis with Rank

[#1]

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]

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

[#3]

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

[swuehl]

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.

[#5]

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

[swuehl]

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

[#7]

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

[swuehl · Accepted solution]

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

[#9]

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