Formula not summarizing in analyzer
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:
Analyzer table:
Let me know if anyone has any ideas.
Answers

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.
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 
There is nothing in sorting, and i am grouping by month.
0 
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?
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 
yes that is correct.
0 
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.
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 
@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 preaggregate as @MarkSnodgrass suggests.
Jae Wilson
Check out my Domo Training YouTube Channel
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Categories
 10.6K All Categories
 1 APAC User Group
 12 Welcome
 36 Domo News
 9.6K Using Domo
 1.9K Dataflows
 2.4K Card Building
 2.2K Ideas Exchange
 1.2K Connectors
 339 Workbench
 252 Domo Best Practices
 11 Domo Certification
 461 Domo Developer
 47 Domo Everywhere
 100 Apps
 703 New to Domo
 84 Dojo
 Domopalooza
 1.1K 日本支部
 4 道場日本支部へようこそ
 22 お知らせ
 63 Kowaza
 296 仲間に相談
 649 ひらめき共有