Hello,
I am looking to do a YoY%, MoM% and QoQ% difference for the following data which has separate columns for ‘Year’ and ‘Month’ in Integer format.
I tried to use the ‘periodOverPeriodDifference’ but it accepts the ‘DateTime’ format in the function parameter. Any inputs on how I could do this or if I can use any other function?
You can either make a calculated field that creates a date field from your year and month:
parseDate(concat(toString(Month), '/01/', toString(Year)), 'MM/dd/yyyy')
Or you can compute the PoP stuff manually, which if you want to make it dynamically update to the latest month all the time would be a little tricky but still possible. For MoM it would be like:
MostRecentYear:
maxOver(Year, [], PRE_AGG)
Most Recent Month:
maxOver(ifelse(Year = MostRecentYear, maxOver(Month, [], PRE_AGG), null), [], PRE_AGG)
ThisMonth:
ifelse(Year = MostRecentYear AND Month=MostRecentMonth, sales, 0)
SameMonthLY:
ifelse(Year = MostRecentYear-1 AND Month=MostRecentMonth, sales, 0)
MoM:
(sum(ThisMonth) - sum(SameMonthLY)) / sum(SameMonthLY)
Thank you Jesse for your inputs. I tried the first method by parsing the date using the formula:
FormattedDate: parseDate(concat(toString(month), ‘/01/’, toString(year)), ‘MM/dd/yyyy’)
Then I used the formatted date in the periodOverPeriodPercentDifference formula as follows:
periodOverPeriodPercentDifference(sum({monthly_first_usage}), FormattedDate, YEAR, 1)
But I see blank values for YoY% in the output as shown below. I see in the example doc, we also have to groupBy the Date, but I don’t see the ‘Group By’ option in the Field wells. Is there anything else I need to do apart from the two formulas to get this working?
The equivalent of Group By in a pivot table would just be the Rows and Columns field wells.
Keep in mind you should expect to see blank values for your firs tyear since there is no data to compare to, but you should se values for newest years.
What should the final visual look like? Are you trying to show a row or column for each month (in a table), or just the most recent month (in a KPI), or something else?
Hi @aakashsp!
Did Jesse’s response answer your question? If not, could please provide a screenshot/sample of what the final visual should look like.
Thanks,
Kellie
Hi Kellie, thanks for checking on this. The periodOverPeriodPercentDifference() is working for YoY% differences but I am trying to find out the MoM% and QoQ% differences between the latest 2 months and latest 2 quarters in the dataset respectively. The output should look something like the MoM and QoQ columns in the screenshot.
Hi @aakashsp!
If you want MoM or QoQ for all months or quarters respectively, then you can use that same periodOverPeriodPercentDifference() function. If you only want the most recent MoM or QoQ, you will need to calculate current month and previous month and then calculate percent difference. I worked up an example of each for MoM in the screenshot provided. QoQ will work the same way. You can also reference this link to help with these types of calculations.
Best Regards,
Kellie
