Hi,
My director of education loves the report that I sent her (thanks everyone for the help), but wants to tweak a few things. Most of them were easy fixes, but one I am struggling with.
This report will be sent weekly and will have the total revenue for that week on it (based on the 'Use last week" filter) using the "Total Ticket Paid Amount" value. However, she would also like the total revenue up to that point in another widget. Meaning after 4 weeks worth of reports, she wants the combine total revenue of those 4 weeks to show up on week 5 report?
I know those widgets exist in the Finance cube. However I am working in Seats & Tickets as she also wants mostly attendance data (which lives in Seats & Tickets). I know you can't mix cubes, so is there a way of changing the formula to one of the existing Total Ticket Paid values to get that compounded revenue info?
I want to restate so I have it clear in my head: you're looking for two widgets:
Is that right?
If so, you could use a custom date filter for #2 to say "Give me all dates (Order Date or Performance Date) that are within this date range (4 weeks ago through end of the year, for instance) AND are up through yesterday". One version of that I have is the one below:
{ "and": [ { "from": "2023-01-01", "to": "2023-12-31" }, { "last": { "count": 366, "offset": 1 } } ], "custom": true}
This one's saying "Filter this widget by dates (performance date) within Jan 1 2023 and December 31st 2023, which are also within the last 366 days offset one (aka, up through yesterday)"
You'd be able to keep #1 widget the way it is, and have the #2 widget keep rolling forward, anchored on whatever date range you want.
Nope, just 1 widget. The "Last Week" filter is in the other widgets that I already built. No help needed there.
Correct on #2. I need Total Revenue to date. But my EDU director wants it broken up by each "Production" (well Season, and then I filter by the Productions). So in the end I will be duplicating this widget 6-7 times with different productions filtered in each for our different education types.
So what she wants, for example, is the total revenue up to say yesterday for this FY for just our Children's Studio classes. Then she wants that same set up for just our Adult Studio classes, etc. etc.
Could you set a filter for widget 2 with something like the custom date filter I posted above, with a 'Value' of 'Total Ticket Paid Amount', and 'Rows' of 'Production Season' or 'Productions'? I don't see why you'd need a separate widget per Season.
I need a separate widget because that's the way my EDU Director wants it
Do you mind if I email you a PDF version, or I can share the dashboard it it's easier? I think I have it exactly how she wants it, but it looks really big and cumbersome. So I'd love some input on how to clean it up a little but still give her exactly what she wants.
Sure! Send me the PDF, and link the .dash file, too.