Calculated dimension for line chart to show only dates in month prior

[linnea_sc]

Hi experts! I am trying to create a line chart that will show only the dates for the prior month. So far I've managed to limit the "start" date of the date range using the following:

=if(aggr(Max([Date.autoCalendar.Date]), [Date.autoCalendar.Date])>=MonthStart(Today(),-1), [Date.autoCalendar.Date])

but this obviously does not limit the end date. Any bright ideas on how I set the end date of the date range?

[Ravi_Nagmal]

Try this

MonthEnd(Today(),-1)

[linnea_sc]

Thanks, but that just limits the end range. I'm looking to show the rolling range of last month only (i.e. if it's December today it should show 1st Nov-30th Nov). Any ideas as to how you limit both the start and end dates in the calculated dimension?

[MayilVahanan]

Hi

Instead of using in dimension, try to use in set analysis.

Sum({<[Date.autoCalendar.Date]= {">=$(=MonthStart(Today(),-1))<=$(=MonthEnd(Today(),-1))"}>}urexpresssionfield)

for dynamic:
Sum({<[Date.autoCalendar.Date]= {">=$(=MonthStart(Max(Date),-1))<=$(=MonthEnd(Max(Date),-1))"}, Year=, Month=, othertimefilter=>}urexpresssionfield)

[linnea_sc]

Thanks - I've already limited the date in the set analysis. The issue is that the chart dimension displays all available dates even when the data is limited through set analysis, which means the data I'm trying to show (eg November) is just a small blip in the chart (not ideal for visualisation purposes). Hence the requirement to limit the dimension.

[Ravi_Nagmal]

can you share more reference in excel or Qlik what is required actually.

[linnea_sc]

 This is what the line chart looks like without limiting the date dimension. What I'm asking is if there's a way of limiting the date using a calculated dimension so that it only displays the date range for which the set analysis is applied.

[MayilVahanan · Accepted solution]

Hi

Not sure, how its display all available dates. Try like below

=if([Date.autoCalendar.Date]>=MonthStart(Today(),-1) and [Date.autoCalendar.Date] <= MonthEnd(Today(),-1) , [Date.autoCalendar.Date])

or
=Aggr(if([Date.autoCalendar.Date]>=MonthStart(Today(),-1) and [Date.autoCalendar.Date] <= MonthEnd(Today(),-1) , [Date.autoCalendar.Date]), [Date.autoCalendar.Date])

And suppress null values

[linnea_sc]

Thanks, this one worked!

=Aggr(if([Date.autoCalendar.Date]>=MonthStart(Today(),-1) and [Date.autoCalendar.Date] <= MonthEnd(Today(),-1) , [Date.autoCalendar.Date]), [Date.autoCalendar.Date])

[Aditya_Chitale]

You need to add extra condition in expression as highlighted below:

=if(aggr(Max([Date.autoCalendar.Date]), [Date.autoCalendar.Date])>=MonthStart(Today(),-1)
and aggr(Max([Date.autoCalendar.Date]), [Date.autoCalendar.Date])<=MonthEnd(Today(),-1)
, [Date.autoCalendar.Date])

Regards,

Aditya