Hello,
I am doing a pivot table as shown below:
I have a filter of months, the stock should not be added while the Sales Units has to be added.
For that I applied a top and bottom filter of Date, so the latest month is taken.
The formula for Sales Units is: sumOver({SALES UNITS},[BUSINESS UNIT], PRE_AGG).
Then I have another calculated field which is MOH = sum({STOCK})/((max({SALES UNITS})
The problem I have is with the total row for MOH, none of the following options calculates the 14 (2400/170) which is the correct value.
I hope you can guide me how to figure out with a solution.
Thank you very much.
Hello @Castro, so you do not want the stock field in the visual, only the MOH field. Is that correct? I don’t think you need to use LAC-W here to aggregate the Sales Units since your Business Unit field is present in the visual.
Rather, you should just be able to use this calculated field:
MOH = {Stock}/{Sales Units}
Then you can just include the Sales Units field as is in the visual. Then, you shouldn’t have any issue aggregating the Total for that field. Since you are aggregating Sales Units as a max in the MOH field currently, I believe it is going to run that way in the Total as well so it will take in the 100 value rather than the sum of 100 and 70. Let me know if this helps!
Hello @DylanM ,
I do need the stock field in the visual, but when I filter more than 1 month this field does not have to be added, it has to be the most recent stock. That is why I use a LAC-W.
Maybe do you know another solution?
Thank you very much.
Hello @Castro, maybe you should be using the sumOver aggregations in the MOH field as well? Then converting the total calculation to sum in the field well.
sumOver({STOCK}, [{BUSINESS UNIT}], PRE_AGG)/sumOver({SALES UNITS}, [{BUSINESS UNITS}], PRE_AGG)
That might resolve the issue you are facing!
