Formula not summarizing in analyzer

JustinB
JustinB Member
in Charting

Hello Dojo,

I have a calculated field I am trying to apply to a time series chart that does not seem to want to summarize. The formula changes based on one of the variables - objective. 

case when `Objective` = 'CLICKS' then 

(case when (sum(`Clicks`) / sum(`Impressions`)) < .0082 then 0
 when (sum(`Clicks`) / sum(`Impressions`)) < .0098 then 1
 when (sum(`Clicks`) / sum(`Impressions`)) < .0107 then 2
 else 3 end)  
 
 
 when `Objective` LIKE 'ENGAGEMENT%' then 

(case when (sum(`Engagements`) / sum(`Impressions`)) < .2203 then 0
 when (sum(`Engagements`) / sum(`Impressions`)) < .2644 then 1
 when (sum(`Engagements`) / sum(`Impressions`)) < .2864 then 2
 else 3 end) 
 
 when `Objective` = 'VIDEO VIEWS' then 

(case when (sum(`Video Views`) / sum(`Impressions`)) < .0489 then 0
 when (sum(`Video Views`) / sum(`Impressions`)) < .0587 then 1
 when (sum(`Video Views`) / sum(`Impressions`)) < .0636 then 2
 else 3 end)
 
 when `Objective` = 'REACH' then 

(case when sum(`Reach`) < 1527494 then 0
 when sum(`Reach`) < 1680243 then 1
 when sum(`Reach`) < 1985742 then 2
 else 3 end) end 
 

When I add this formula to my chart and try to trend it by date, each of the dates repeats. It looks to repeat for each objective. I would like this score to show the summarized score for all objectives. Oddly enough, the data table at the bottom shows what I want, but in analyzer it repeats.

Data table at bottom:

datatable domo example.PNG

Analyzer table:


card table example.PNG

Let me know if anyone has any ideas.

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    edited June 2021

    Check to see if you have any fields in the Sorting section of the Analyzer. That will throw of how Domo can aggregate the data in the card. Also, check the Date Range Filter and what it is grouping by.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • JustinB
    JustinB Member
    edited June 2021

    There is nothing in sorting, and i am grouping by month.


    analyzer w objective.PNG


  • MarkSnodgrass
    MarkSnodgrass Coach

    If you add your objective column to the table card, will that show that there is a month "repeating" because there is a different objective type?

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • JustinB
    JustinB Member

    yes that is correct.

  • MarkSnodgrass
    MarkSnodgrass Coach

    My first suggestion would be to do the calculation in Magic ETL, but that may not work for you depending on how you want the card to function as far as filtering

    You might try restructuring your case statement and see if that does the trick. If you can get your objective within your division calculation so that your are only dividing once, that might work. Sorry, I don't any good sample data to test this out with.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @JustinB by building your aggregation INSIDE the CASE statement you're applying the CASE statement after aggregation. (i know that sounds like i said the same thing twice...


    point being, what you're asking for is "after i aggregate the data, i want to substitute the results with a number and then i want to aggregate again."


    but you can't... because you already aggregated.

    when you put Objective on the Axis, it should look like your 'CASE statement is working' but as soon as you take it off, it will look like 'it isn't working.' and again, that's because you're expecting an aggregation AFTER CASE.


    IF Clicks and Impressions are on the same row, and it's appropriate to rate each row of data as Clicks / Impressions then you can rewrite your BM as 


    sum(
case
when objective = ... AND clicks / impressions = .75 then 0
when objective = ... AND clicks / impressions = .82 then 1
)

    Note this evaluates and assigns a score per ROW not per DAY.

    If you want to assign a row per day, you have to pre-aggregate as @MarkSnodgrass suggests.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories