Formula not summarizing in analyzer

JustinB

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.

MarkSnodgrass

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.

JustinB

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

MarkSnodgrass

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?

JustinB

yes that is correct.

MarkSnodgrass

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.

jaeW_at_Onyx

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