topic Re: Rank + If in Set Analysis in QlikView

Rank + If in Set Analysis

martinmichalski — Mon, 04 Sep 2017 11:28:55 GMT

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 — Mon, 04 Sep 2017 13:09:12 GMT

Please provide the sample data.

Kushal_Chawda — Mon, 04 Sep 2017 13:14:54 GMT

what is expression for SalesPreviousPeriod, SalesCurrentPeriod?

suryaa30 — Mon, 04 Sep 2017 13:58:04 GMT

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 — Wed, 06 Sep 2017 07:38:54 GMT

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

martinmichalski — Wed, 06 Sep 2017 07:39:37 GMT

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

suryaa30 — Wed, 06 Sep 2017 08:16:55 GMT

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

IF(Account=vDimList,Account)