multiple IF statement in set analysis formula

[Zarzo]

Hello,

I have a dataset with KPIs, which are measured differently, some of them - monthly, some - quarterly (in dataset then is the first month of the quarter). For each KPI data comes in a columns:

KPI_key : KPI_1, KPI_2, KPI_3

Period : 2023-01, 2023-02, 2023-03

Comparative Period : MONTH-2, MONTH-3, QUARTER-2, QUARTER-3

Actual : 0.15%, 2569, 555999 (some are measured %, some by units)

Type : %, unit

In a table I need to show the last periods date(its either a previous month (or -2 months) or previous quarter (or -2 qiarters)) and the Actual results for that previous period.

Also I need to show the period before previous period, so it could be -2 month from today or -2 quarters from today.

To get the needed period date I have made variables 

vPreviousPeriod :

If([Comparative Period] = 'MONTH-2' AND not IsNull(Actual), MAX(MonthStart(AddMonths(date(Today(), 'YYYY-MM'), -1))),
If([Comparative Period] = 'MONTH-3' AND not IsNull(Actual), MAX(MonthStart(AddMonths(date(Today(), 'YYYY-MM'), -2))),
If([Comparative Period] = 'QUARTER-2' AND not IsNull(Actual), MAX(MonthStart(AddMonths(date(Today(), 'YYYY-MM'), -(Mod(Month(date(Today(), 'YYYY-MM'))-1, 3) + 1)))),
If([Comparative Period] = 'QUARTER-3' AND not IsNull(Actual), MAX(MonthStart(AddMonths(date(Today(), 'YYYY-MM'), -(Mod(Month(date(Today(), 'YYYY-MM'))-1, 3) + 1)*2)))))))

vComparetivePeriod:

If([Comparative Period] = 'MONTH-2' AND not IsNull(Actual), MAX(MonthStart(AddMonths(date(Today(), 'YYYY-MM'), -2))),
If([Comparative Period] = 'MONTH-3' AND not IsNull(Actual), MAX(MonthStart(AddMonths(date(Today(), 'YYYY-MM'), -3))),
If([Comparative Period] = 'QUARTER-2' AND not IsNull(Actual), MAX(MonthStart(AddMonths(date(Today(), 'YYYY-MM'), -(Mod(Month(date(Today(), 'YYYY-MM'))-1, 3) + 1)*2+1))),
If([Comparative Period] = 'QUARTER-3' AND not IsNull(Actual), MAX(MonthStart(AddMonths(date(Today(), 'YYYY-MM'), -(Mod(Month(date(Today(), 'YYYY-MM'))-1, 3) + 1)*3+1)))))))

These formulas works when I put it in a table to get the needed date, but when I put the variables to get the needed Actual result, I get all zeros. For Actual I use formula (for Comparative period I only change the variable, but result is still zeros):

IF(Type='%' ,
round(sum( {$ <Period = {'=$(=$(vPreviousPeriod ))'} >}Actual), 0.01) & '%',
round(sum({$ <Period = {'=$(=$(vPreviousPeriod ))'} >} Actual), 0.01)
)

If someone have ideas, please share 🙂

[Chirantha]

Hey!

Instead of using nested if conditions in the variable, you can try to create flags in the load script for your Comparative Periods. Then, you can leverage these flags in your set analysis expressions as spoken in this other post Solved: Create flag in load script - Qlik Community - 867716. 

1) You can try create two flags for each KPI in your load script as follows

LOAD KPI_key,
     Period,
     Comparative Period,
     Actual,
     Type,
     If([Comparative Period] = 'MONTH-2' OR [Comparative Period] = 'QUARTER-2', 1, 0) AS FlagPrevPeriod,
     If([Comparative Period] = 'MONTH-3' OR [Comparative Period] = 'QUARTER-3', 1, 0) AS FlagCompPeriod,
FROM YourDataSource;
 

2) Then you can use the created flags in your set analysis expressions as follows

IF(Type='%' ,
  round(sum({$ <Period = {'$(=Max(Period))'}, FlagPrevPeriod = {1}>} Actual), 0.01) & '%',
  round(sum({$ <Period = {'$(=Max(Period))'}, FlagPrevPeriod = {1}>} Actual), 0.01)
)

// For Comparative Period
IF(Type='%' ,
  round(sum({$ <Period = {'$(=Max(Period))'}, FlagCompPeriod = {1}>} Actual), 0.01) & '%',
  round(sum({$ <Period = {'$(=Max(Period))'}, FlagCompPeriod = {1}>} Actual), 0.01)
)


 Please let us know if this works! 🙂

[Zarzo]

Thank you for idea, but unfortunately Comparative Period : MONTH-2, MONTH-3, QUARTER-2, QUARTER-3 cannot by split into FlagPrevPeriod and FlagCompPeriod as you suggested. This column shows the comparative period for each KPI, for example:

KPI_1 - MONTH-2

KPI_2 - MONTH-3

KPI_3 - QUARTER-2

KPI_4 - QUARTER-3

That's the biggest issue for me 🙂

[Chirantha]

Alright, lets try the following

MappingTable:
Mapping Load * Inline [
KPI_key, Comparative Period
KPI_1, MONTH-2
KPI_2, MONTH-3
KPI_3, QUARTER-2
KPI_4, QUARTER-3
];

LOAD KPI_key,
     Period,
     Comparative Period,
     Actual,
     Type,
     If([Comparative Period] = ApplyMap('MappingTable', KPI_key, ''), 1, 0) AS FlagKPIPeriod
FROM YourDataSource;

and then you can then use the FlagKPIPeriod in your set analysis expressions:

IF(Type='%' ,
  round(sum({$ <Period = {'$(=Max(Period))'}, FlagKPIPeriod = {1}>} Actual), 0.01) & '%',
  round(sum({$ <Period = {'$(=Max(Period))'}, FlagKPIPeriod = {1}>} Actual), 0.01)
)
 

[Zarzo · Accepted solution]

I found a work around upgrading the Load Script with a separate table [Summary] where I used FirstSortedValue() script function. With this script I filter data for actual and previous period, this worked as I needed in my case. 

[Summary]:
LOAD
KPI_Key,
FirstSortedValue(Period, -Period, 1) as ActPeriod,
FirstSortedValue(Actual, -Period, 1) as ActActual,
FirstSortedValue(Target, -Period, 1) as ActTarget,
FirstSortedValue(Period, -Period, 2) as PrevPeriod,
FirstSortedValue(Actual, -Period, 2) as PrevActual,
FirstSortedValue(Target, -Period, 2) as PrevTarget
Resident [KPI_actuals]
Where Not IsNull(Actual)
Group By KPI_Key; 

Hope it will be useful for someone, too!