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

[#1]

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

[llauses243]

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

[#3]

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

=5

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

[llauses243]

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.

[#5]

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

[llauses243 · Accepted solution]

Josep,

I leave solution, pls to see image adjust

Luis

[#7]

Luis,

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

[#8]

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.