Date From Filter overrides the date from adddatetime Calculated Field

Question & Answer
,
1

I used adddatetime to create a calculated field that returns the date coming from the filter minus 1 month using this formula: addDateTime(-1,'MM', {calendar_date}) but when i use the calculated field in another formula, calendar_date which comes from the filter overrides previous month date calculated field. How do i make this work correctly?

2

@nelsonajayi -
Can you further explain and provide example of how the override is happening. What are the calculated fields and filters. What is the process where you see the override?

1 Like
3

Thank you for your reply @Kellie_Burton. Here In this formula ((distinctCountOver({account_id},[{calendar_date},feature], PRE_AGG))/(distinctCountOver({account_id},[{Previous_Month},feature],PRE_AGG)))-1, I want to calculate MoM count of accounts. calendar_date is the current date from filter and Previous_Month is current date -1 month. See from the screenshot that AccountMoM is 0%, because the deonminator distinctCountOver({account_id},[{Previous_Month},feature],PRE_AGG)is still doing a count of the current month (calendar_date)
image

4

any updates on this?

5

Hi @nelsonajayi

I think you have two options here. You could use periodOverPeriodPercentDifference outlined in this blog.

Or you could use ifelse() logic in your calculation in combination with parameters (with a control) to then use the addDateTime() function in your denominator.

Let us know how it goes.
Thanks

6

Thank you for your response @bergqdou . I have earlier tried periodOverPercentDifference as seen in this formula: periodOverPeriodPercentDifference(distinctCountOver({account_id},[feature], PRE_AGG),{calendar_date}, MONTH, 1) but this throws an error “Field distinctCountOver(…) must be aggregated for table calculations”. To get around this i instead did: periodOverPeriodPercentDifference(max(distinctCountOver({account_id},[feature], PRE_AGG)),{calendar_date}, MONTH, 1) but this will not be displayed on my visual as it throws “table calculation attribute reference(s) are missing in field wells” error. Is there something I’m not doing right?

7

@nelsonajayi
I believe it’s because you do not have the date field in the field wells. Can you try to add it, select the dropdown in the field wells and hide it?

1 Like
8

Hi @nelsonajayi ,
We hope the reply from @bergqdou worked for you.
Let us know if this is resolved. And if it is, please help the community by marking the reply as a “Solution.”
Many Thanks,
Andrew