How to show 6 months data using the set analysis in the dimension

[varunreddy]

Hi Guys,

I am trying to show 6 months data on the report by writing set analysis using the "Add Calculated Dimension". Can someone please help me with the expression in the dimension or suggest any other option?

Current Date -  Dec 2017. I want to show all dates from June. If I select November 2017, I would like to see all the dates from May 2017.

Expression I have been trying:

=Aggr(Only({$<Date={">=$(=date(max(Date),'MMM YYYY'))<=$(=Date(AddMonths(max(Date),-6),'MMM YYYY'))"} >} Date),Date)

Thanks in advance!

Cheers,

Varun

[sunny_talwar]

Are you making selection in Date field? If you are, have you ignore selection in Date field from your expression?

[varunreddy]

Hi Sunny,

I have attached the source file and QVW file to the actual discussion. Please take a look and advise what I am missing.

My requirement: When I select a Date, I want to see past 6 months data.

Cheers,

Varun

[sunny_talwar · Accepted solution]

Two things

1) Create your MonthYear field like this rather than using MonthName function. MonthName creates a string which makes it unusable in the set analysis....

Date(MonthStart(Date), 'MMM YYYY') as Date,

2) I asked you initially also that don't add spaces between >= and $() or <= or $()... just don't add any spaces in your set modifier

=Aggr(Only({<Date = {'>=$(=vPrevious5MonthYear)<=$(=vCurrentMonthYear)'}>}Date),Date)

[varunreddy]

Hi Sunny,

I got my mistake. Will use Monthstart going forward instead of MonthName function.

Many Thanks,

Varun