Thanks for a great question.
I made a video with it:
and attached the workbook.
Oh, my God! I am going to be crazy.
I try to replicate your answer, but I could not all day long. I was looking into every calculation to see what are the difference.
At last I've seen it.
I send you two packeges workbook. In your file "percent diff in market share" all work fine. I prefer to have the Difference of Market Share just as a subtraction because Market Share is computing as a Brand Percent. Following your example the Difference YoY Share was -2,5%, 0% and +2,5%, as I would like to have.
But in my example I have 20%, 23% and 33%. All the computation has exactly the same formula, I promise. The only thing that it is different is that in my example the last item (Diff YoY Share) has a mark in "Computing using ..." Date (my Year's variable), and you do not have any mark at this.
Now my question is easy, how can I uncheck Date in the Computing using menu?
Thank you very much for your fast and very understanding answer. The video and the file have help me a lot.
You did not set the partitioning correctly. If you watch the video again, at the 2:00 mark, I go through the partitioning you want set. You will want to set it the same way as in the video for your new field "Diff YoY Share".
From the "example.twbs" you just attached:
1. Bring up the context menu for the pill "Diff YoY Share", and select "Edit Table Calculation"
2. Change the drop-down that is at the top of the dialog, called "Calculated Field", changing it from "Diff YoY Share" to "Market Share"
3. Change the drop-down for "Compute using" from "Date" to "Brand"
What is going on is you need to set the partitioning differently for each table calc, and this dialog allows you to do that. Because you want "Market Share" to compute using "Brand", and the "Diff YoY Share" to compute using "Date".
Is the pace of the video too fast, or how do you think I could make it clearer?
I did all you say. But I do not when, I have mark "Diff YoY Share" with "Compute usaing ..." (right click over the variable name) as a Date, and then even I go to the Edit the Calculate Field and say "Diff YoY Share" has to usign Date it doesn't work.
In your example you don't have this mark and that is the only difference I have found. Please try to modify my "Diff YoY Share" example because I could not. Even I redo your steps in the Example File, I can not get the right results. And I do not know what I am doing wrong.
In the "Edit Table Calculation" dialog box, are you changing the drop-down called "Calculated Field", changing it from "Diff YoY Share" to "Market Share"?
When "Market Share" is the selected "Calculated Field" in the dialog box, you want to set the "Compute using" drop-down in the dialog from "Date" to "Brand"
When you have set the partitioning differently as I have instructed here, and in the video, you will no longer see the dot/mark next to "Date" when you are only looking at the context-menu option for compute using. When you set the compute using from the context menu, adding the dot, you are setting the partitioning the same for each calculated field. In order to get the result you want, you need the partitioning set different for each calculated field. Each level of aggregation gets its own partitioning.
Thank you very much for your kind answer. Now I understand my problem.
I set the compute using from the context menu, adding the dot, so I am setting the partitioning the same for each calculated field.
Is there any way to remove the dot in the compute using context menú?
> "Is there any way to remove the dot in the compute using context menú?"
No, you must use the dialog box, because the dot means all calculated fields within this pill will use the same partitioning. And the dot is removed when the pill contains calculated fields that are set to different partitioning. You can only set different partitioning to the calculated fields within the pill by using the dialog box.
Thank you very much.
Hi Joe -
I also need difference in market share year-over-year. It seems that my table calculations are structured like your examples, but the math isn't working. What am I missing?
npdclean.twbx 115.5 KB
Wonderful explanation. Thank you!