Hi all,
Half the problem is solved and provide the solution:
I have several dimensions: event_code, previous_event_code, current_days_to_event, days_left_to_event, and ticket_sales_grouped_by_days_left_to_event.
For example, if I select the event_code MON2401 in the dashboard, its previous event code is MON2301 (from the previous year). These are two music festivals, but the event date is not on the same day and month in both years, which is why I changed to the days_left_to_event dimension to analyze them aligned. That's why the current_days_left is 68, but if I select MON2301 instead of MON2401, the current_days_left_to_event is -296, because I calculated that field previously with the current day. Additionally, days_left_to_event goes up to 0, while if I select MON2401, it goes up to 67.
What I need is several things:
Considering I select MON2410 in a dashboard filter, a general KPI for any chosen event_code that shows the total sales from MON2301 (the previous event) until there were 67 days left, and a table with 10 rows and two columns, where the first column shows the last 10 days from 76 to 67 in this case, and the second column shows the sales for that day.
I am trying this for the KPI but doesn't work:
sum( {<event_code = p(previous_event_code), days_left_to_event = {"<=67"}>} ticket_sales_grouped_by_days_left_to_event )
As for the table this, that also doesn't work:
Dimension: days_left_to_event
Measure: sum({<event_code = p(previous_event_code), days_left_to_event = {">=67<=76"}>} ticket_sales_grouped_by_days_left_to_event)
This worked:
Measure: {<event_code = p(previous_event_code), days_left_to_event = {">=$(=current_days_left_to_event)<$(=current_days_left_to_event + 10)"},year_event=, month_event=, event_name=>}
ticket_sales_grouped_by_days_left_to_event
Any help much appreciated!!
