Partition in Beastmode or Possible Another Solution

DSides
DSides Member
in Beast Mode

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?

Tagged:

Best Answer

  • brycec
    brycec Coach
    Answer ✓

    You want to use a FIXED function.

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

    Was this comment helpful? Click Agree or Like below.
    Did this comment solve your problem? Accept it as the solution!

Answers

  • brycec
    brycec Coach
    Answer ✓

    You want to use a FIXED function.

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

    Was this comment helpful? Click Agree or Like below.
    Did this comment solve your problem? Accept it as the solution!

Categories