Help in set analysis

[ali_hijazi]

Hello

I have a pivot table with Dimension is Month_Year

How can I pass the dimension value into the Set Analysis in order to give different date ranges on different rows of the report.

where my expression is count({<TRANS_DATE={">= START_DATE <=END_DATE"}>}CUSTOMER)

START_DATE would be 1 year back prior to dimension value and END_DATE is one day prior to month start of dimension value

hope I was able to clarify the requirement

please advise

[Anonymous]

Hi

Are you looking for set analysis expression for last year and lastmonth ?

For lastmonth:

count({$<TRANS_DATE={"$(=Date(AddMonths(Max(Month_Year), -1), 'MMM-YYYY'))"}>} CUSTOMER)

For Lastyear:

count({$<TRANS_DATE={"$(=Date(AddMonths(Max(Month_Year), -12), 'MMM-YYYY'))"}>} CUSTOMER)

For dates between last year and last month :

count({$<TRANS_DATE={">=$(=Date(AddYears(Max(Month_Year), -1), 'MMM-YYYY')<=$(=Date(AddMonths(Max(Month_Year), -1), 'MMM-YYYY')) )"}>} CUSTOMER)

or

count({$<TRANS_DATE={">=$(=Date(AddMonths(Max(Month_Year), -12), 'MMM-YYYY')<=$(=Date(AddMonths(Max(Month_Year), -1), 'MMM-YYYY')) )"}>} CUSTOMER)

[ali_hijazi]

No this approachDate(AddYears(Max(Month_Year), -1) is the same on each row

I want max(month_year) to take into consideration the value of month_year in dimension

[kaushiknsolanki]

Hi,

Try the expression given in below link.

Set Analysis with Max Date with Time-based Dimension

Regards,

Kaushik Solanki