Beastmode | All seperate rows instead of one summed row

user046467
user046467 Member
edited July 3 in Beast Mode

I've the following beastmode:

case when DATEDIFF(Select Columns.Date,Date) >0 then sum(Select Columns.Totaalprijs)/COUNT(DISTINCT Email) else 0 end

But instead of one row I get multiple rows.


I tried adding 'sum' to other places in the beastmode but I'm receiving either an error or the same result.

Can someone shed a light on this? Thanks 😁

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    try putting g your case inside your SUM statement:

     sum(case when DATEDIFF(`Select Columns.Date`,`Date`) >0 then `Select Columns.Totaalprijs` ELSE 0 END)
    /COUNT(DISTINCT Email)
    

    You're evaluating each row one at a time with the case statement outside of your aggregation which is why you're getting multiple records.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    try putting g your case inside your SUM statement:

     sum(case when DATEDIFF(`Select Columns.Date`,`Date`) >0 then `Select Columns.Totaalprijs` ELSE 0 END)
    /COUNT(DISTINCT Email)
    

    You're evaluating each row one at a time with the case statement outside of your aggregation which is why you're getting multiple records.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Ahh I see, thanks that works!