Dynamic 'previous month' Selctions in Set Analysis with Pivot

[TheresaB_4]

Hi everyone,

I created this pivot. But now it is showing all the months. If I want it show only 3 months.

Previous month, current month and next month. August 2023/Septemeber 2023/October 2023
How do I do this.

[TheresaB_4]

I have tried this, but it does not work.

The [MeteringPointSupplyEndDate] is my date field

=MonthName([MeteringPointSupplyEndDate]=Today()-1,'MMMM-YY')

[sbaro_bd]

Hi @TheresaB_4 ,

If your aim is to show this by default, I suggest you to create three specific measures (one measure for each case). You can simply manage that with a set analysis.

Regards.

[TheresaB_4]

Hi @sbaro_bd ,

Thank you, can you give me an example to create one measure for previous month.

Regards

Theresa

[sbaro_bd]

You can take a look these following expressions for inspiration.

// Variables for the previous period
LET vPreviousStartMonth = MonthStart(Addmonths(TODAY(),-1))
LET vPreviousEndMonth = MonthEnd(Addmonths(TODAY(),-1))

// Calculation expression
SUM({<MeteringPointSupplyEndDate={">=$(vPreviousStartMonth)<=$(vPreviousEndMonth)"}>} Measure)

If you want to dynamically change the current period (replace Today() by a selected period/month), create a master calendar and add it yo your model : https://community.qlik.com/t5/QlikView-App-Dev/Creating-A-Master-Calendar/td-p/341286.

Regards.

[Aditya_Chitale]

@TheresaB_4 ,

Try below expression in your dimension expression of Field MonthYear and uncheck "include null values" option:

=Aggr(Only({<MonthYear={">=$(=Date(addmonths(today(),-1),'MMM YYYY'))<=$(=Date(addmonths(today(),1),'MMM YYYY'))"}>}MonthYear),MonthYear)

If your MonthYear field is in text format, convert it into Date format first.

Regards,

Aditya