I have some data that relates to each day of the year.
I aggregate and display this data in a Pivot table in Workshop.
It would be relevant for the workflow of the users to see the “last month” or the “last year”: relative to now.
How can I show this information in the pivot table ? Is there a way to show the “last month” in some easy way ?
Hi @VincentF.
you are poking into the vasp nest here - this is currently not possible with vanilla pivot table functionality. I am (selfishly 
) pinning my thread on enhancements to unleash the potential of pivot tables here where I propose one feature exactly solving your case at hand and where I would love to get PD input on.
Currently, we are solving this by pre-aggregate data to specific defined dimensions (like region, account, product) per month and join the “last year” (current year month -12 months) sales as individual column. This way you can set up your pivot table formula for YoY Growth = sum(sales) - sum(sales-1yr) which will look like this:
Since your data is at daily aggregate, you would self-join in two columns, one for sales_-30 days (Month over Month growth) and sales -365 days (Year over Year Growth).
Note: with derived properties, I believe it is possible to aggregate the last year sales on the fly in workshop. However, you still have to to pre-aggregate your data, so that you have the fixed monthly/quarterly/yearly sum and the pk_last_year for the search around to aggregate the sales_-1yr. You could not just have your backing data to be each individual sales transaction…
Hope this helps!
@VincentF one more thought that came to my mind you need to watch out for.
Depending on how your data is structured you need to choose the proper join condition. What I mean by that is, imagine this case of product chocoloate. This has sales in every month of 2024, but only in may and june of 2025.
If you would bring in the last_year value only by left-joining pk with LY_pk you would miss all the yellow marked last_year sales due to this month not existing in the current year. So you would calculate a false last_year_sales sum, since you are not incorporating those months where you had sales in last year, but don’t have in current year.
In order to prevent this, you either first explode your left table for all products over all possible time dimensions (and set sales to 0 on these) so that the left-join will always work.
Or you just go for the Outher join and do a bit of cleaning afterwards.
See here with the outer join, all month in 2024 that have either sales in 2024 or in the last year reference 2023 (yellow) are appearing. 2024_06 is not getting last year sales since 2023_06 is not existing (this is ok).
The only thing left here to do is to clean up your dimension columns for those cases, where you don’t have current year, but last year sales (For example:
pk = coalesce(ok, right_pk)
Thanks for the detailed answer @Phil-M !
We are facing a similar need and are approaching it differently for the moment (might change approach and get to stg closer to yours if we end up being blocked).
For each order, we have a delivery date and have derived from it a week number (1-52), month number (1-12) and day number (1-365).
This is allowing us to put side by side the same week/month from a year ago.
What is missing here for a great UX (hello PD !) is the ability to configure the column groupings from the front-end, just like we can do it for row groupings
Are you aggregating your data from order item level to daily (by delivery date?).
If not, I don’t think this approach will work since you might have multiple orders on the same delivery day. And if you then join in your -1yr data (this has to be on a daily aggregate right?) you would get the same -1yr order value on multiple order items and basically double count that.
And I agree column grouping collapsability would come in super handy 
What confuses me on this is, the enhancement to configure multiple column groupings got only introduced recently - why has the collapsability not been done along with it 
(probably more effort… but grouping ↔ collapsability are just naturally linked in my opinion)
