Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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.
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
Josep,
I leave solution, pls to see image adjust Luis |
Luis,
I made the adjustments you had recommended in your latest jpg, and that corrected the last inconsistancy that we had.
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.