I wan to create one single column in the end which would give the MoM difference between the last two available months in the data in this case nov and october.
@thomask
@Aditya_Raj, you can use periodoverperiod function to calculate the MoM difference and % difference. here is an example -
periodOverPeriodPercentDifference(sum(Sales), {Order Date}, MONTH, 1)
1 Like
Hi Deepak,
I tried your solution but it is not giving the right difference also, if you see the below image it showing MOM 2 times after each month where as I want just 1 column in the end which shows the difference. FOr example the first row (-0.2)-(-0.5) = -0.05 this should be the MoM difference that it should be displaying.
Can you please help me with this ?
hi @Aditya_Raj,
i am curious if pivot table is how you want to visualize the MoM difference because if you add a measure under value field wells, that measure will be repeated for each of the column field well values, that’s the reason you are looking at multiple MoM columns. Can you double check the MoM calculation to confirm if the period attribute is identified as MONTH and offset value is 1 to make sure it is calculating month over month difference. Pivot table also support table calculation without requiring to use additional calculated field for MoM, see below screenshot. This will still repeat the difference values for each of the month column vales.
You can add a measure and click on the drop down next to that measure value and select the add table calculations as difference.
1 Like
Hi @Aditya_Raj
Did Deepak’s solution work for you? I am marking his reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!
Deepak and other’s answers are correct. Adding that if you want just 1 MOM column you would need to do it this way: Period Over Period and Other Date Comparison Calculations (YoY, MoM, YTD, Rolling 90 days, etc)
@Jesse And what if I’m using a calculated field? It gives me an error about nesting of agg functions.
For example: I want the MoM of a field like this: sum([Field 1])/(sum([Field 1])+sum([Field 2]))
@DanielSousa If Fields 1 and 2 are already aggregated, then you dont need the sums. Can just do field1/(field1+field 2)