Using date filter as input for Beastmode calculated field

BenjaminPapes · 2023-02-22T13:21:11+00:00

There was an error rendering this rich post.

Hi,

I have a Beast mode calculated field where I use MAX('Sales_Date') to keep the calculations fresh automatically (as we are getting the data with 1-2 months lag so I can't use current date).

Problem is when users start changing the date range filter in the dashboard to a range not including the MAX('Sales_Date') in the dataset, the calculation fails.

Is there a way to use the date range filter as an input in Beast mode? A way that would allow me to use 'Selected_Date' instead of MAX('Sales_Date')?

Thank you!

Find more posts tagged with

Date

Filters

Accepted answers

MichelleH

@BenjaminPapes It was worth a shot at least. Another option would be to create a global variable where the user can select a year to replace the max date like this:

sum(case when YEAR(`Sales_Date`) = `Year_Variable` then `Sales` else 0 end)

All comments

MichelleH

@BenjaminPapes If you don't want the maximum date to be affected by dashboard filters, I'd recommend adding it as a dataset column in your dataflow instead of a beast mode. That way it will not be affected when you filter by sales date.

BenjaminPapes

@MichelleH Thank you very much for your reply.

Sadly that does not do the trick because if the users touch the date range filter and want to see historical results, the hard coded date from the dataset gets out of filtered range and all results end up being 0.

To be more specific on what I am trying to achieve - users should be able to use the date range filter and it should affect the maximum date in the calculation.

But writing it as a calculated field does not work for some reason, following is a simplified line which should return the sales for the current year.

But I always get the following error:

Maybe I there is something wrong with the code?

MichelleH

@BenjaminPapes I see what you mean. The error you're encountering is because your calculation has an aggregate inside and aggregate. Does it still give you an error if you remove the outer SUM from the beast mode and set the aggregation in the drop down when using it in the card?

BenjaminPapes

@MichelleH Removing the SUM from beast mode results in same error. Keeping the SUM but removing the MAX function and using the maximum date from the dataset works (but prevents using the date range filter).

Does not work:

Works but prevents date range filter:

Not sure why though.

MichelleH

@BenjaminPapes It was worth a shot at least. Another option would be to create a global variable where the user can select a year to replace the max date like this:

sum(case when YEAR(`Sales_Date`) = `Year_Variable` then `Sales` else 0 end)

BenjaminPapes

@MichelleH

Thank you very much! This works like a charm.

Two additional minor issues have arisen.

First - is there a way to set the default variable value to the 'Max Sales Date' from the dataset? (the following suggestion does not work). Currently I would have to go in and manually change it every time there is an update.

Second - now there is the variable control and the default date picker both in the dashboard. Is there a way to remove/hide the default date picker? The variable control overrides the default one, but it still might create confusion for the users.

MichelleH

@BenjaminPapes I'm glad that worked for you! As for your other two issues, I'm not aware of any way to accomplish what you're looking for, but those would be great suggestions for the Ideas Exchange.

Gionniz

I am doing something similar with variables, for a Period-over-Period calculation, and I would need to synchronize my selection within the variable and the global date selector (instead of having to hide it) moreover it would be nice to have a current_period dynamic seletion in the var (lastDay, lastWeek, lastMonth etc...)

Hope this will be available soon, cheers