Using date filter as input for Beastmode calculated field

BenjaminPapes
BenjaminPapes Member
in Beast Mode

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!

Tagged:

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

    @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)


Answers

  • MichelleH
    MichelleH Coach

    @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
    BenjaminPapes Member

    @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.

    image.png

    But I always get the following error:

    image.png

    Maybe I there is something wrong with the code?

  • MichelleH
    MichelleH Coach

    @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
    BenjaminPapes Member

    @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:

    image.png

    Works but prevents date range filter:

    image.png

    Not sure why though.

  • MichelleH
    MichelleH Coach
    Answer ✓

    @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
    BenjaminPapes Member

    @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.

    image.png

    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.

    image.png


  • MichelleH
    MichelleH Coach

    @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
    Gionniz Contributor

    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

Categories