Set analysis filtering

[martynwb]

Hi, I was looking for some support.

I would like to change a chart in Qlik Sense using set analysis to only look at data for the last 12 months. I have a Period dimension which has an ID, which is sequential. I also have a variable which holds the current month ID. My plan was to use an if statement to check if the Period was within the last 12 period IDs and if it was, to perform the calculation. The calculation works alone but the following code produces no data. Any idea?

=
If(PeriodID>($(vMaxPeriod)-12),

(Count({<trnRawStatus={'completed'}>}trnRawStatus) + Count({<trnRawStatus={'passed'}>}trnRawStatus)) / Count (trnRawStatus)
)

Thanks

Martyn

[pedrobergo]

Hi @martynwb ,

Can you show some samples about PeriodID and vMaxPeriod fórmula?

Consider that PeriodID is a Data field and vMaxPerido has Max(PeriodID)

I prefere to use Set Analysis directly at measures, using Addmonth function, like this:

Count({<trnRawStatus={'completed','passed'},PeriodID={">$(=Date(Addmonths(Max(PeriodID),-12)))"}>} trnRawStatus)
/ Count ({<PeriodID={">$(=Date(Addmonths(Max(PeriodID),-12)))"}>} trnRawStatus)

[],

Pedro

[martynwb]

The Date() function isn't working here because PeriodID is an autonumber and it cannot translate it. So the code that you provided still returns all of the data.

This is the Period dimension:

[records1]:
LOAD
[id],
[field_21] as PeriodID,
//[field_21_raw],
[field_2] as MonthName,
//[field_2_raw],
[field_4] as MonthNumber,
//[field_4_raw],
[field_5] as Year,
//[field_5_raw],
[field_19] as MonthYear
//[field_19_raw],
//[__FK_records] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_records])
ORDER BY [field_21] DESC
;

This is the variable: $(vMaxPeriod) = Max(PeriodID)

Thanks

[stevejoyce]

If your vMaxPeriod variable set in the app or load script.  Can you check if the definition begins with a dollar sign so your max(period) is calculated globally, it should be "=Max(Period)"

[martynwb]

It's set in the app and it's global.

I might be over complicating it though. I only want to show all data within the last 6 months (from today). So I guess I may not need vMaxPeriod. I just need to work out how to identify if a month is 6 months before today and include those six months of data. The period is Month, then year (MMM YYYY)

[stevejoyce]

If you are going to do a date comparison, just make sure Period is loaded as a date field and you can format it to how you want using, something like date(floor(date(makedate(right(Period,4), left(Period 3), 1), 'MMM YYY')), 'MMM YYY') as Period.

the you should be ok doing Period > $(=addmonths(today(),-6))