Rank and aggr function in set analysis not working

[#1]

Hi,

I have a requirement to find the median of 25% of total count, 50% of total count and so on for every quarter. I cannot implement the ranking function in script because the count will differ based on filter selection.

For the data in the attached file, I would like to have the result of the Straight table as below.

The Table named Final shows the correct value if one QuarterYear is selected but not when no filters are selected (Also the table shows only one QuarterYear always).

Please let me know if you need more information

Thanks,

Malai

[JonnyPoole]

Played around with it for a bit

I think you are missing more than one quarteryear because the Set Modifier is not evaluating Rank at the QuarterYear number.

i disabled all expressions except one and put in the following and it now seems to bring back all quarterYears

=Median({<Site = {"=  aggr( Rank( sum(-Days)),YearQuarterNo,Site) <= round( Count ( total <QuarterYear> Days)*0.25)"}>} Days)

i did NOT check the median values for accuracy.... please see attached and verify numbers to see if they are still wrong. Hopefully we are down to 1 issue.

[#3]

Hi Jonathan,

Thanks for your help. As you said, now all the QuarterYear are showing up in the table but unfortunately the Median numbers are not right. Also when I do it for median of 50% count I see the same numbers as 25% repeating for that expression as well.

[JonnyPoole]

K.

Working on the numbers , i went with a search mask where both sides use aggr()

=median(  {<Site = {"=  aggr( Rank( sum(-Days)),YearQuarterNo,Site)-1 <= round( aggr( NODISTINCT Count ( DISTINCT Site),QuarterYear)*0.25)"}>} Days )

=median(  {<Site = {"=  aggr( Rank( sum(-Days)),YearQuarterNo,Site)-1 <= round( aggr( NODISTINCT Count ( DISTINCT Site),QuarterYear)*0.5)"}>} Days )

It seems to pay off except for 1 or 2 issues:

A) I'm getting a different value than you for Q1-15 ( 365 vs 370) ... can you break down the exact values that fall into this bucket that would lead to a value of '370'

B) For reasons not yet known, my ranking expression starts at '2' (so i'm subtracting '1')  . Strange and may be tied to A. 

Anyways please let me know the answer to A) .. 

[#5]

I found issue B, I think Rank 1 was considered for Site with null values. So I changed the expression as below and removed the -1 from the expression.

=median(  {<Site = {"=  aggr( Rank( If(isnull(Site)=0,sum(-Days))),YearQuarterNo,Site) <= round( aggr( NODISTINCT Count ( DISTINCT Site),QuarterYear)*0.25)"}>} Days )

But for issue 1 I still see a problem (Though not with Median 50 but with Median 25):.

The expected answer should be:

The data for Q1 - 15 is:

Since Count of 25% is 2, Rank 1 and 2 should be considered. In this case Rank 1 = 360 days and Rank 2 = 365 days and the median should be (360+365)/2 = 362.5

But the result from our 25% expression is 365. Is this because rank 2 and rank 3 have same values and it is considering the whole result set for the median?

Thanks for your help.

[JonnyPoole]

i think so.. trying to tweak the 2nd/3rd parameters to rank the 2nd 365 as a higher rank (rather than equivalent rank to first 365).

Works a little different in the Set Modifier search mask...

stay tuned

[JonnyPoole · Accepted solution]

This will blank out the duplicate rank (blank the 2nd 365)

=median(  {<Site = {"=  aggr( Rank( If(isnull(Site)=0,sum(-Days)),4,2),YearQuarterNo,Site) <= round( aggr( NODISTINCT Count ( DISTINCT Site),QuarterYear)*0.25)"}>} Days )

=median(  {<Site = {"=  aggr( Rank( If(isnull(Site)=0,sum(-Days)),4,2),YearQuarterNo,Site) <= round( aggr( NODISTINCT Count ( DISTINCT Site),QuarterYear)*0.5)"}>} Days )

[#8]

Hi Jonathan,

When I implemented the expression in my main application, it was again not showing all the dimensions. Tried different things and finally got it working with the below expression.

=Median(IF(aggr(Num(Rank(if(isnull(Days)=0,-Days),4,1)),QuarterYear,Site)<=Aggr(Round(Count(Total<QuarterYear> Days)*.25),QuarterYear,Site), Days))

Thanks again for your help!

Malai