Help with Formulas and Count when <=

user04125

Hi all,

 

I am working on building a card that displays a count of cases that are less than 1, 2 and 7 days old from creation date. Currently using this formula for case age:

 

SUM(UNIX_TIMESTAMP(CAST(NOW() AS DATETIME)) - UNIX_TIMESTAMP(CAST(`Created Date` AS DATETIME))) / 86400 

This returns a decimal form in days which is what we want.

 

Next looking to do a count of cases that fall under 1 day using the calculation above to distinguish how old they are.

 

I've tried using this equation"

(CASE
WHEN (SUM(UNIX_TIMESTAMP(CAST(NOW() AS DATETIME)) - UNIX_TIMESTAMP(CAST(`Created Date` AS DATETIME))) / 86400) <= 1 THEN 1
ELSE 0
END)

 

(CASE
WHEN (SUM(UNIX_TIMESTAMP(CAST(NOW() AS DATETIME)) - UNIX_TIMESTAMP(CAST(`Created Date` AS DATETIME))) / 86400) <= 2 THEN 1
ELSE 0
END)

 

 

(CASE
WHEN (SUM(UNIX_TIMESTAMP(CAST(NOW() AS DATETIME)) - UNIX_TIMESTAMP(CAST(`Created Date` AS DATETIME))) / 86400) <= 7 THEN 1
ELSE 0
END)

 

With no luck. Is there a solution in beastmode or does this need to be done in our warehouse with SQL

 

 

 

 

user03357

You just need to move your SUM to the outside of the case statement so that it sums the 'THEN' count like this:

 

SUM(CASE
WHEN ((UNIX_TIMESTAMP(CAST(NOW() AS DATETIME)) - UNIX_TIMESTAMP(CAST(`Created Date` AS DATETIME))) / 86400) <= 1 THEN 1
ELSE 0
END)