Create a field for WoW (or MoM, depending on the parameter) change

Question & Answer
, ,
1

Hi,

I’m trying to create a field that shows WoW/MoM (depending on the selected parameter value) of a metric feedbacks .

The table has a dimension field in the row and a Period field (the formula is ifelse(${Period}='Weekly', truncDate('WK', {feedback_date}), ${Period}='Monthly', truncDate('MM', {feedback_date}), truncDate('YYYY', {feedback_date}))) in the column. This can be easily achieved using the table calculation directly, but I wanted to create a calculated field to use the WoW/MoM difference. I tried using periodOverPeriodDifference(feedbacks, Period, MONTH, 0) but for some reason it returns 0…

The end result would be what’s in the screenshot: I want recommendation type in the row, date (monthly or weekly, depending on the parameter I select in the column, and feedbacks, #avg rating, rating change and rating change * feedbacks 4 metrics.

Screenshot 2024-03-06 at 1.34.55 PM
Screenshot 2024-03-06 at 1.34.55 PM1684×320 39.9 KB

I already have a pivot table created by using the table calculation directly on the field to calculate PeriodOverPeriod percent change.

Screenshot 2024-03-07 at 11.33.53 AM
Screenshot 2024-03-07 at 11.33.53 AM1386×210 16.3 KB

Any thoughts? Thanks!

2

did you try changing the offset to 1 in the formula periodOverPeriodDifference ?

3

oh that works!

But I wanted to use the parameter to determine the whether it’s weekly or monthly. Here is the formula:

periodOverPeriodDifference(distinct_count({feedback_id}),Period, ifelse(${Period} = 'Weekly', WEEK, ${Period}='Monthly', MONTH, ${Period}='Yearly', YEAR), 1)

But I got error by using the ifelse function.

It also doesn’t work if I use the parameter directly as periodOverPeriodDifference(distinct_count({feedback_id}),Period, ${Period}, 1). as I got this error

Expression {{argumentName}} for function {{functionName}} has incorrect argument type {{incorrectArgumentType}}. Function syntax expects {{functionSignature}}.

Any ideas on that?

4

Can you try the other way round like :
ifelse( condition1, periodOverPeriodDifference formula based on condition1 ,
condition2, periodOverPeriodDifference formula based on condition2…)

Period formula does not support parameter, so you have to condition it likewise using the period keywords directly based on the paramater value.

1 Like
5

Thanks! That works. Here is what i have

ifelse(
${Period} = 'Weekly', periodOverPeriodDifference(avg(rating),Period, WEEK, 1),
${Period} = 'Monthly', periodOverPeriodDifference(avg(rating),Period, MONTH, 1),
periodOverPeriodDifference(avg(rating),Period, YEAR, 1)
) * distinct_count({feedback_id})

I was following this post to use the parameter…I think it works there but not in my case

1 Like