Hi everyone,
I'm working on a pivot table to replace/revamp a report that goes out right now. One of the big asks is that the report shows the first perf date and last perf date of each production season. So it would look like the attached image but before the ticket counts and dollar amounts there would be a column for first perf and last perf. I'm stumped! Has anyone been able to make this happen?
Thanks!
Madeline
Hi Madeline,
The best way to achieve this with a date format that you can control would be to talk to consulting about 2 custom date fields that return the first and last performance date within each production season. Alternatively, you could create a VALUE that looks like a date...
Here I've got these numeric parts of the Performance Date yielding a value in the format YYYYMM.DD. Then taking the MIN and MAX of that resulting value returns the first and last performance date within each production season:
MIN ( [Calendar Year] * 100 + [Calendar Month] + ([Calendar Day Of Month] * 0.01) )
Best,Chris
Chris Wallingford Product Owner Tessitura Network office: +1 888.643.5778 x553 chris.wallingford@tessituranetwork.com
Thanks Chris! This is great as a stop-gap. I do have one question. In your example, it brought back 201712.10 for one of the values. How do I make sure that the 0 is at the end? When I ran this one of my values came back as 202004.3 and it should have a 0 at the end.
Madeline Dummerth (Past Member)
Is the date you are showing here is 4/30/2020 (as written here in the states)? Have you checked the formatting to see if you can force the system to display 2 decimal points?
haha - boy do I feel silly! Yep - that solved it!
I like your question. I've had blind spots like this in the past.
I'd like to thank you for asking. We now have a more complete description here on how to make this work.
--Tom
Was the Calendar Day of Month added in a later version than 15.0.1? I can't seem to find it .