Rank + If in Set Analysis

[martinmichalski]

Hi Community,

I am having some trouble using the rank functionality in set analysis.

Here is an example of what I am trying to do:

Sum({<

[Account] = {"=Rank( If( $(SalesPreviousPeriod) >0 AND $(SalesCurrentPeriod) <=0, $(SalesPreviousPeriod), 0) <= 20"),

[Date] = {"<CurrentPeriodMinDate >=CurrentPeriodMinDate-1"}>} $(vSales))

-The rank functionality is being used because we need to apply dimension limits (top 20) in a Pivot Table.

-I need the If statement to rank just those accounts which had sales in the previous period but not in the current one.

-I tried pre calculating the If statement in a variable (vRankAcc) and it didn't work.

Sum({< [Account] = {"=$(vRankAcc)"),

[Date] = {"<CurrentPeriodMinDate >=CurrentPeriodMinDate-1"}>} $(vSales))

Do you know if there is a different way to do it?

Thanks in advance

[rohitraut]

Please provide the sample data.

[Kushal_Chawda]

what is expression for SalesPreviousPeriod, SalesCurrentPeriod?

[suryaa30 · Accepted solution]

Calculate  the Rank function and list the accounts required using CONCAT.

Give that variable as Dimension.

Example:

vDimList=CONCAT( RankCondition   Account)

In Dimension:

IF(Account=vDimList,Account)

[martinmichalski]

They are Sales Amount expressions with set analysis filtering the period (current year and previous year)

[martinmichalski]

Thank you. We are going with a similar approach, as precalculating the concat accounts has better performance.

[suryaa30]

Do let me know how are you evaluating the equality assuming vDimList has multiple values?

IF(Account=vDimList,Account)