Sum of all instances of dates more than 30 days old

DJP
DJP Member
in Magic ETL

Hello all, 

 

I'm attempting to sum all of the instances of dates that are more than 30 days older than the current date to determine which users of my app are no longer active. Below is my beast mode code:

 

SUM(CASE WHEN DATEDIFF( CURDATE(),Max(`Last Sign In`)) > 30 THEN 1 ELSE 0 end)

 

However, ever time I run this command it returns:

 

"An issue has occurred during processing. We are unable to complete the request at this time."

 

I've tried both count and sum but have not been able to get this statement to work. When I remove the count or sum it does produce the desired 1 or 0 next to the users name but I would like to see how many of these users are no longer active.

 

Any help would be much appreciated!

Comments

  • NewsomSolutions
    NewsomSolutions Domo Employee

    When are you getting that error?  At the execution of the BM calc in the card or did you use it in a Filter?  If  a Filter, that would fail b/c it contains an aggreation.

     

    If just executing it, you may want to think about setting up the ETL/dataset so "most recent sign in" aka Max(last sign in) is its own field...b/c I think...I think...the aggregation there is what is erroring out. May not be so don't hate me if I'm wrong.  but I'd try that.

Categories