Conditional CASE Indicator Widget

[ushadow91]

I'm attempting to create an indicator widget that calculates the total number of overdue tickets.  It would use two formulas to determine the difference between the total due date day allotment(Ticket must be completed in 30 days as an example) and then the actual days it took to close the ticket(Actually took 32 days as an example). I have a formula, Days Difference, that calculates the difference between due date allotment, and the actual days. The indicator widget I'm attempting to create would calculate and display the sum of all the tickets that were over due(tickets where the days to close the ticket were greater than the due date. 

I am running into some syntax errors and I'm not confident the sum will be accurate without additional filtering 

sum([All Tickets],
CASE
WHEN DDIFF([Days Difference]) >1
THEN sum([All Tickets])
WHEN DDIFF([Days Difference]) < 1
THEN 0
end)

[HamzaJ]

Hey @ushadow91 

What syntax error are you getting? The first error I see is the DDIFF. It doesnt include an end-date (in your case today). It should be something like MAX(DDIFF(NOW(),Days Difference)) . DDiff's need to be in an aggregate such as sum, min, max. Unless you are on Windows or have the Analytical Engine disabled, then you can do it without an aggregation

[ushadow91]

Thanks @HamzaJ  I'm getting

• "Unexpected Token '[' in FunctionParameters Definition".
• "Unexpected token')' in Boolean expression"  

sum([All Tickets],
CASE
WHEN MAX(DDIFF(NOW()[Days Difference])) >1
THEN sum([All Tickets])
WHEN MAX(DDIFF(NOW()[Days Difference])) < 1
THEN 0
end)

[HamzaJ]

You forgot the , . Try the following:

sum([All Tickets],
CASE
WHEN MAX(DDIFF(NOW(),[Days Difference])) >1
THEN sum([All Tickets])
WHEN MAX(DDIFF(NOW(),[Days Difference])) < 1
THEN 0
else 0
end)