I took a quick look at this and it's technically possible to do this in Tableau, however
a) It's much easier to compute this in Excel because Excel can do arbitrary cell references.
b) The year grand total showing a single value is not something that is easily displayed in Tableau because of how Tableau does layout (it doesn't merge columns or rows like Excel does).
This is one of those cases where letting Excel do what it's good at and not forcing Tableau into something it's not good at might be the better choice. I know we don't always have that option, but it's worth exploring in your use case.
I'll solve this when I have time, it'll take a couple-few days before I can get to it (I've got to take care of my paying clients first . A few questions in the meantime:
1) Is the layout of the Required Output field in Excel the true output, or is this an in-between output while the data is further manipulated and/or a different chart type is used (such as a line chart, heat map, etc.)
2) Are there quarters when the data is sparse, i.e. no records for a given Sub-Category?
3) What is your data source? Excel, SQL Server, etc.?
4) Are you able to build a custom view or query on that data source?
5) What is the actual # of sub-category equivalents in your view?
6) What is the underlying number of records?
Thank you for your reply, below are the answers for your questions:
1. Those Outputs are the true outputs which I need to show in cross tab structure as well as in bar graph also (as per client requirement).
2. There can be some sub categories in an quarter which does not have any data.
3. My data source is postgresql.
4. We can build a custom SQL.
5. Currently its 10, in future it can be more, so I am will to make it dynamic.
6. Around 50K.
Will try to resolve this in a meantime. Please let me know if you are able to make it.
Did you get a chance to try it.. I am sorry for stretching you in this... But I really need a help!!
I tried it, the addition of with first column digit and base digit is taking place by using: ZN(SUM([Quantity])) + ZN(LOOKUP(SUM([Quantity]), -1))
But subtracting column's min is getting difficult. Can you please help me out!!
Thanks in advance.