Forum Discussion

ushadow91's avatar
ushadow91
Data Storage
07-19-2023

Conditional CASE Indicator Widget

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)

 




dashboards & reporting

3 Replies

Replies have been turned off for this discussion
  • HamzaJ's avatar
    HamzaJ
    Data Integration
    07-20-2023

    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's avatar
      ushadow91
      Data Storage
      07-20-2023

      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's avatar
        HamzaJ
        Data Integration
        07-20-2023

        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)