Hi All,
I have a situation ;
My original result dataset is
Country || Region || MMYYYY || Sales.
and the data is for past 6 months
I got to show the current 3 months sales ranking and the previous 3 Sales ranking and also the ranking is based on Drill (The drill is based on Country and Region)
I have grouped the sales based on current 3 months and previous 3 months and applied the ranking.
If the drill is at the top level ., for example., For All Country and All Region I could able to show the ranking as below
Country – Region – Mth_Grouping – Ranking
US – West – Current 3 months – 7
US – West – Previous 3 months – 10
US – East – Current 3 months – 3
US – East – Previous 3 months – 12
…
…
But if i drilldown to one particular country and a particular region ., I could able to show the ranking for previous 3 months but the current 3 months ranking is always one ., irrespective of whatever i do.
like as follows.,
Country – Region – Mth_Grouping – Ranking
US – West – Current 3 months – 1
US – West – Previous 3 months – 10
The variable has the same calcualtion for both current and previous 3 months.
Ranking
=If([Mth_Grouping] = “Previous_3_months_sales” ) Then Round(NoFilter(Rank(([Previous_3_months_sales]);[Region];[Country]);Drill);0) ElseIf ([Mth_Grouping] = “Current 3 months”) Then Round( NoFilter( Rank(([Current_3_months_sales]);[Region];[Country]);Drill);0) Else 0
Here, the variables [Previous_3_months_sales] and [Current_3_months_sales] are calcualted based on the required correpsonding mmyyyy
Just for analysis purposes, I checked the sales whether it is showing properly in the drilldown mode for a particular country and region… It is showing properly the corresponding sales values.
I wonder how to apply this filter on drill down. I am in Xi 3.1 SP2
Forum Experts please help
bdharma (BOB member since 2008-07-07)