Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
martinmichalski
Contributor III
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
suryaa30
Creator II
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)

View solution in original post

6 Replies
rohitraut
Creator
Creator

Please provide the sample data.

Kushal_Chawda

what is expression for SalesPreviousPeriod, SalesCurrentPeriod?

suryaa30
Creator II
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)

martinmichalski
Contributor III
Contributor III
Author

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

martinmichalski
Contributor III
Contributor III
Author

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

suryaa30
Creator II
Creator II

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


IF(Account=vDimList,Account)