topic Re: Rank and aggr function in set analysis not working in QlikView

Rank and aggr function in set analysis not working

Wed, 25 Mar 2015 18:30:37 GMT

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

Re: Rank and aggr function in set analysis not working

JonnyPoole — Wed, 25 Mar 2015 20:31:37 GMT

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.

Re: Rank and aggr function in set analysis not working

Wed, 25 Mar 2015 20:53:15 GMT

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.

Re: Rank and aggr function in set analysis not working

JonnyPoole — Wed, 25 Mar 2015 23:50:14 GMT

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

Re: Rank and aggr function in set analysis not working

Thu, 26 Mar 2015 00:26:02 GMT

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.

Re: Rank and aggr function in set analysis not working

JonnyPoole — Thu, 26 Mar 2015 00:33:15 GMT

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

Re: Rank and aggr function in set analysis not working

JonnyPoole — Thu, 26 Mar 2015 00:47:33 GMT

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 )

Re: Rank and aggr function in set analysis not working

Thu, 26 Mar 2015 06:40:10 GMT

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