Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have been successfully using the variable extension to cycle through different dimensions as below.
The variable is called $(date_agg) and is pointing to 1 of 4 different columns week_date, month_date,quarter_date,year_date.
I have an expression which has one of the date columns hard coded in(red below).
sum({$<[product]={'original'},[week_date.autoCalendar.Date] = {"$(=Max([week_date.autoCalendar.Date],))"}, Month = , Year = , Date = >}metric
I want to edit this set analysis to be based on whichever date column the variable is currently set to.
I have tried a few different things eg. just referencing the variable
sum({$<[product]={'original'},[date_agg.autoCalendar.Date] = {"$(=Max([date_agg.autoCalendar.Date],))"}, Month = , Year = , Date = >}metric
or
sum({$<[product]={'original'},[$(date_agg).autoCalendar.Date] = {"$(=Max([$(date_agg).autoCalendar.Date],))"}, Month = , Year = , Date = >}metric
I have also tried defining a master dimension date_ag_dim with the expression $(date_agg) and used this dimension in the expression:
sum({$<[product]={'original'},[$date_ag_dim .autoCalendar.Date] = {"$(=Max([$date_ag_dim .autoCalendar.Date],))"}, Month = , Year = , Date = >}metric
But all three fail.
Wondering if anybody can see where the flaw in my process is?
Try this
Sum({$<[product] = {'original'}, $(='[' & $(date_agg)& '.autoCalendar.Date]') = {"$(=Max($(='[' & $(date_agg)& '.autoCalendar.Date]')))"}, Month = , Year = , Date = >}metric
Unfortunately this solution did not work for me however I did figure it out
Sum({$<[product] = {'original'}, $(date_agg) = {'$(=$(recent_date))'}>}metric
I defined a variable 'recent_date' as date($(=max($(date_agg),1)))
thanks for setting me in the right direction.