Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Search instead 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?

Thanks in advance

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

Please provide the sample data.

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