topic Re: Aggr(Rank function: how to get around null values in 2 dimensions, when 'Suppress when null values' doesn't work for both dimensions in QlikView

Aggr(Rank function: how to get around null values in 2 dimensions, when 'Suppress when null values' doesn't work for both dimensions

Mon, 23 Jan 2012 17:36:50 GMT

The [MIX] field contains some null() values that I can exclude by selecting "Suppress when null values"

and that allows my Customer Number Rank to work fine because the Nulls are taken out of the possible records to be ranked.

However when I want to Rank the [MIX] seperartly in addition to the [Customer Number], it will only work when i make the

[Commodity Type] Selection Spa.

The must be a way around having to make the [Commoidty Type] selection for "Both" rankings to work.

I have attached a QVW that shows both rankings working with the Selection,

How can i get both Rankings to work Without Having to Make a Selection

Expression to Rank [MIX]

=IF(Aggr(Rank(Aggr(Sum($(sCYear)[Unit Price]), MIX),3, 1), MIX)<= 10,

Aggr(Rank(Aggr(Sum($(sCYear)[Unit Price]), MIX), 3, 1), MIX)

)

Expression to Rank [Customer Number]

=IF(Aggr(Rank(Aggr(Sum($(sCYear)[Unit Price]), MIX,[Customer Number]),3, 1), MIX,[Customer Number])<= 10,

Aggr(Rank(Aggr(Sum($(sCYear)[Unit Price]), MIX,[Customer Number]), 3, 1), MIX,[Customer Number])

)

[Commodity Type] includes

Accessories

Demo

Others

Parts

Spa

Re: Aggr(Rank function: how to get around null values in 2 dimensions, when 'Suppress when null values' doesn't work for both dimensions

llauses243 — Tue, 24 Jan 2012 03:14:03 GMT

Hi,

To add " and [Commodity Type] = 'Spa' " to each IF

For simplicity i had used Variable1 & V2

Pls to see image attached

Good luck, Luis

Re: Aggr(Rank function: how to get around null values in 2 dimensions, when 'Suppress when null values' doesn't work for both dimensions

Tue, 24 Jan 2012 17:49:50 GMT

Luis,

Thanks for your reply,

I have an excel sheet that has a list of all my variables, I added the below three new variables to that sheet

vMXRank

=Aggr(Rank(Aggr(Sum($(sCYear)[Unit Price]),MIX),3,1),MIX)

vMXCustRank

=Aggr(Rank(Aggr(Sum($(sCYear)[Unit Price]),MIX,[Customer Number]),3,1),MIX,[Customer Number])

vRank

Then I reloaded, replaced my original calculated dimensions with the new formulas that use the new variables instead of the aggr(rank expression

and I Keep getting

"Allocated Memory Exceeded" which usually means something is not working.

I'll upload my changed QVW See the one labled with Variables

Aggr(Rank function: how to get around null values in 2 dimensions, when 'Suppress when null values' doesn't work for both dimensions

llauses243 — Tue, 24 Jan 2012 19:28:19 GMT

Josep,

In general the variables have 2 uses, as calculators or textual template

Then to define without starting with "=", example vMXRank must be Aggr(... not to use =Aggr(...

Luis.

Re: Aggr(Rank function: how to get around null values in 2 dimensions, when 'Suppress when null values' doesn't work for both dimensions

Tue, 24 Jan 2012 20:06:56 GMT

Luis,

i made the adjustments you recommended and now it does work however the MX RNK or Rank of the Mix

starts at Rank 2

But when i make the selection [Commoidty Type] Spa in the App, then the MX RNK corrects itsself to Starting at Rank 1 instead of 2.

Why is that and how can this be fixed?

I have included my latest QVW version and changed the variable to "e" instead of "v" to indicate expression

Re: Aggr(Rank function: how to get around null values in 2 dimensions, when 'Suppress when null values' doesn't work for both dimensions

llauses243 — Wed, 25 Jan 2012 15:47:47 GMT

Josep,

I leave solution, pls to see image adjust

Luis

Re: Aggr(Rank function: how to get around null values in 2 dimensions, when 'Suppress when null values' doesn't work for both dimensions

Wed, 25 Jan 2012 17:20:49 GMT

Luis,

I made the adjustments you had recommended in your latest jpg, and that corrected the last inconsistancy that we had.

Re: Aggr(Rank function: how to get around null values in 2 dimensions, when 'Suppress when null values' doesn't work for both dimensions

Mon, 30 Jan 2012 20:34:18 GMT

this worked great for Top 5 Mix and Top 5 customers however for some reason when i select Top 10 or Top 20

the below new variables were created,

eRank5 = 5

eRank10 = 10

eRank20 = 20

If i used $(eRank5) as my variable in each calculated dimension, to select top 5 as you can see in the attached QVW it works for MIX and for Customers within each mix.

But when i change $(eRank5) to $(eRank10) the MIX sorts correctly but the customer numbes sorts the first 5 correctly but then the next five customer numbers seem to be mixed. This seems wierd.