Expression that would not be affected by the filtering

[Tom6]

Hi!

At the time when a user opens the report, I want to show only the result of the last 6 months that are in my dataset.

And I managed to do that (the result is in the third column):

Expressions in my dimension columns:

1 column - just a date: [Date.autoCalendar.YearMonth]

2 column - the difference between max date and previous dates: (max(TOTAL([Date]))-(AddMonths([Date], -0)))

3 column - IF statement that put dates if the difference is less than 155 days: IF(((max(TOTAL([Date]))-(AddMonths([Date], -0)))<155),[Date.autoCalendar.YearMonth,)

So, about my problem. If I want to filter, for example, results for 8 months. I get only the last 6 months data from max filtered date.

Maybe someone knows how to write an expression that would not be affected by the filtering period? And I could filter out as many periods as I want.

[Oleg_Troyansky]

Hi,

If I understand your question correctly, you want your formula to disregard selections in the field Month. If this is what you are looking for, then it can be achieved with a simple Set Analysis condition:

{<Month=>}

Tis condition can be used in any aggregation function like sum, count, max, etc:

Sum( {<Month=>} Value)

Allow me to invite you to my session on Set Analysis and AGGR at the virtual Masters Summit for Qlik, that will take place online on March 1st. I will teach advanced techniques for using Set Analysis and AGGR.

Cheers,