Custom Roll up dates
In Pivot table I would like to be able to roll-up by custom dates.
Specifically we would like to view revenue of clients based on Financial year and not fiscal year
Callum
shared this idea
-
Callum
commented
Thanks Mariya!
That is very helpful -
Mariya
commented
Hi Callum,
Thank you for reaching out! It sounds like being able to set a custom year to roll up dates by would be extremely helpful, and this is an excellent suggestion for an enhancement. I have logged a ticket with our Product team for them to review and consider implementing this functionality in a future project.
Once this feature is released to the system, you will be notified via email!
In the meantime, there is a 'workaround' you could implement to achieve this functionality. You would need to create a Calcualted or a Triggered Text field on the table where your Dates 'live' and set a custom year through a formula. A general example of the syntax would look as follows, I specified the Financial Year as April 1 through March 31:
if(and({Date}>"2021-03-31", {Date}<"2022-04-01"), "FY 2021-22",
if(and({Date}>"2020-03-31", {Date}<"2021-04-01"), "FY 2020-21",
if(and({Date}>"2019-03-31", {Date}<"2020-04-01"), "FY 2019-21",
if(and({Date}>"2018-03-31", {Date}<"2019-04-01"), "FY 2018-19",
"FY before 2018-19"))))In this example, I set FY 2018 through 2021, you should be able to add/remove years from the formula as needed (each line sets a year). If you then select this field under 'Columns' in your Pivot configuration, you should be able to see totals for the specified Financial Year.
If you have any additional questions, please do not hesitate to reach out to us at support@trackvia.com, or by calling into our direct line + 1 (800) 673-3302, our Hours of Operation are Monday through Friday, 7:00 AM - 6:00 PM MST.
Kind Regards,
Mariya