Nested Beast Mode With Variables

Scrapper140
Scrapper140 Member
in Beast Mode

I am trying to build a dashboard that will allow the user to select a customer, date range and metric to return a value that will be compared in a table to another value which is average of that same metric for a of predefined group of customers over another date range. The data for this currently resides in the same data set. I am trying to minimize hard coding in formulas.

Best Answer

  • ggenovese
    ggenovese Coach
    Answer ✓

    Hi @Scrapper140

    If I'm understanding correctly, this may help get you started. Assuming your data is structured like my example below, and if you wanted to calculate the average for metric when the customer is not the selected customer and the date is not the selected date then you would build a case statement. Example:

    Sample Data

    image.png

    Beast Mode

    AVG(CASE WHEN `Customer` != `My Customer Variable` AND `Date` != `My Date Variable` THEN `Metric` END)

    It sounds like your rules are more complicated than my "not equal to" example but hopefully this will help you get started

Answers

  • ggenovese
    ggenovese Coach
    Answer ✓

    Hi @Scrapper140

    If I'm understanding correctly, this may help get you started. Assuming your data is structured like my example below, and if you wanted to calculate the average for metric when the customer is not the selected customer and the date is not the selected date then you would build a case statement. Example:

    Sample Data

    image.png

    Beast Mode

    AVG(CASE WHEN `Customer` != `My Customer Variable` AND `Date` != `My Date Variable` THEN `Metric` END)

    It sounds like your rules are more complicated than my "not equal to" example but hopefully this will help you get started

  • Scrapper140
    Scrapper140 Member

    @ggenovese Thanks for responding. Making the Customer a variable(and variable control) rather than a dimension filter made all the difference!

Categories