Partition in Beastmode or Possible Another Solution

DSides

Hi,

I have a dataset that joins spends from our Facebook account by date with leads that came in that day. Since we have multiple leads per day the "Facebook Spend By Day" will be in the data set multiple times.

In a beast mode I want to get a look at the total Facebook spend all time. I am using this right now: SUM(DISTINCT `Facebook Spent By Day`), but I know if there is an amount spent that is the same as another from a different day, then it won't be counted each time. I tried to do a partition by date like this: SUM(DISTINCT `Facebook Spent By Date`) OVER (PARTITION BY `Date`) but that isn't working the number much higher than it should. does anyone have an idea of how to fix this?

brycec

You want to use a FIXED function.

SUM(MAX(`Facebook Spend By Day`) FIXED (BY `Date`))

brycec

You want to use a FIXED function.

SUM(MAX(`Facebook Spend By Day`) FIXED (BY `Date`))