Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for
Did you mean:
Contributor III

## Rank + If in Set Analysis

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?

1 Solution

Accepted Solutions
Creator II

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)

6 Replies
Creator

what is expression for SalesPreviousPeriod, SalesCurrentPeriod?

Creator II

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)

Contributor III
Author

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

Contributor III
Author

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

Creator II

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

IF(Account=vDimList,Account)

Community Browser