Heyo,
Hoping someone can’t point me in the right direction. I’ve got a custom cube with our daily sales report data in it. I would like bucket sales totals into events that have played off and those that have not. I’m hung up on how to retrieve today’s date and use compare to the perf_date. Then bucket by those criteria. In SQL I would do something like
SUM
(CASE
WHEN perf_dt < GETDATE() THEN tickets
END) as played_off,
WHEN perf_dt >= GETDATE() THEN tickets
END) as future,
SUM(a.cyo_tix)
Ultimate goal would be this output
Single Tix
CYO Tix
Sub Tix
Total Tix
Played Off
545
98
1200
1843
Future
1248
894
3594
5736
Hi Jonathan,
This post uses values to achieve similar results: (+) Membership Comparative Reporting - Discussions - analytic Coffee! - Tessitura Network
A value filtered on Performance Date in the Last 360 days, vs another in the Next 360 days might do the trick. You'll want to consider that the Last will default to including today (offset: 0) while Next defaults to starting tomorrow (offset: 1). Clicking into the Advanced tab for each of these filters, you can adjust that for Last to include only through yesterday (offset: 1) and Next to include today (offset: 0).
However, you're looking for a dimensional / categorical field that flags events as being past or future. Does the [Status] field from the performance details dimension work for your use of that field? Or, given that this is a custom cube, can you write your SQL into the ETL to create another column on your performance details dimension?
Thank. I'll look into this. We should be able to modify the ETL if needed.