cancel
Showing results for
Did you mean:

# Conditional CASE Indicator Widget

7 - Data Storage

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)``````

3 REPLIES 3
12 - Data Integration

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

7 - Data Storage

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)``````

12 - Data Integration

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)``````
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Developers Group:

Product Feedback Forum:

Submit a Support Request

The Legal Stuff

Have a question about the Sisense Community?

Email community@sisense.com