Beast mode to only show matching creative types in grouped bar chart?

We are using a grouped bar chart to compare client and typical engagement rates by creative type (see 1st screen grab). The report is filtered by a SalesQuote field where client results have one value and typical results='Banner Average'. These are unique rows in the data set (see 2nd screen grab). There are six 'Banner Average' rows for every possible creative type (these represent the past 6 months of performance across all creatives) Because the creative types in client campaigns vary, the report should display client & typical results only for the creative types run. We are filtering by creative type in the card but would prefer to automate this - meaning have a responsive filtering mechanism that adjusts as creative types are added. Any thoughts on how to approach this in Beast Mode (or even ETL)?

Best Answer

  • trafalger
    trafalger Coach
    Answer ✓

    Would something like this work?

    Case when SUM(SalesQuote) <> 0 and SUM(BannerAverage) <> 0 then 'Show' else 'Hide' end

Answers

  • How are you calculating the average? Could you just go a group-by in the ETL by Creative type and then append it to the dataset? Then if anything new is added it would just get added automatically.

  • cthtcc
    cthtcc Member

    HI @trafalger the 'Banner Average' value in the SalesQuote field for typical performance is misleading. We're actually calculating an overall monthly engagement rate across 6 months rather than an average for typical performance of creatives

  • cthtcc
    cthtcc Member

    @trafalger I'm not looking so solve for calculating roling averages. We have 16 different creative types with more coming, so a join to actual performance data rows increases the storage impact we dont' want.

    What we need to solve for is whether it is possible to automate filters so the card displays an engagment rate only when both the client SalesQuote value and the Banner Average field have data for the same Creative Type.

  • trafalger
    trafalger Coach
    Answer ✓

    Would something like this work?

    Case when SUM(SalesQuote) <> 0 and SUM(BannerAverage) <> 0 then 'Show' else 'Hide' end