Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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
Hi,
Try the expression given in below link.
Set Analysis with Max Date with Time-based Dimension
Regards,
Kaushik Solanki