1. Join the data with itself as shown below. (Optional: Rename the object names to CY and LY so that they are easy to identify.)
2. You should now have the data as needed.
Hope this helps. A 10.5 workbook is attached.
base_cy_ly.twbx 130.0 KB
Hello @Hari Ankem,
Thanks for your answer.
Today, I cant do this method. My base is separated by orders x ordered items, I need to find some way of calculating within the table itself.
I didn't understand this statement: "My base is separated by orders x ordered items". Please clarify with some data.
It's like this:
So, basically the revenue is at an order-item level. So, do you need to compare the revenue YOY for each order and/or item combination? Will the order number be the same across different years? If so, you can always include the order and/or item too to the join condition shown above. If not, please explain what your needs are.
Yes, I need to compare the revenue YOY for each DAY, WEEK, MONTH (data nivel). The importance of order-item level is to show the data in all different categories here. The order number is not the same across the years (its different).
OK. Hope this helps.
I have done a Union of the data 4 times, once each for YEAR, MONTH, WEEK and DAY. This data is then joined with another Union of the same data 4 times. I have used a calculated join. You can include an additional join for an Item as required.
I am using the following LOD calculations:
I have a parameter to select the period and have an additional filter to ensure the data join is correct. (I did observe some incorrect data being joined, and could not figure that out for now. So, had to use the Data Join filter as shown below).
You now have the required outputs:
You can probably achieve the above functionality using Custom SQL on Excel files on Windows systems. Since, I am on a Mac, this ended up being slightly complex.
base_cy_ly.twbx 111.6 KB
Very nice alternative.
But I have some problems:
-Orders are from different categories, I can create some primary key to make the correct join;
-My data source is connected to Tableau Server and because of this I cant make joins...
Is there any formula-only method?
Are you using any database? If yes, you may want to check if it supports analytic/window functions. Then you may hopefully be able to modify your queries and get the desired solution.
As for a formula-only method, I can explore further but cannot guarantee a solution right away.