Using case statement to make a value if null and run a calculation if not
I'm trying to find the availability of an application for each month using two date fields in our incident data. I've got it to give me a number when the data exists (the calculation is summing the difference between the times for a given month > subtracting that number from the total number > dividing that difference by the total time in the month). However, i've not been successful in adding a '1' (100% availability) to months when there are no fields that affect the calculation. This leaves gaps in my line graph and isn't a great representation of the data (if it has 100%, it would be nice to show that instead of a blank space for that month).
I'm using a case statement to try to run the calculation when the field exists, and place a '1' when the fields are NULL to represent 100% availability. Below is what I have. Does anyone have any suggestions? Any help would be greatly appreciated.
CASE
WHEN `time2` IS NULL THEN '1'
ELSE ((DAYOFMONTH(LAST_DAY(`date_created`))*1440) SUM(TIME_TO_SEC(TIMEDIFF(`time2`,`time1`)))/60) / (DAYOFMONTH(LAST_DAY(`date_created`))*1440)
END
Comments

Hi
I think you don't have the same data type returning on both CASE branches, you are returning a string and a number.
Try :
CASE
WHEN `time2` IS NULL THEN 1
ELSE ((DAYOFMONTH(LAST_DAY(`date_created`))*1440) SUM(TIME_TO_SEC(TIMEDIFF(`time2`,`time1`)))/60) / (DAYOFMONTH(LAST_DAY(`date_created`))*1440)
ENDTell me how it goes.
Ricardo Granada
MajorDomo@Lusiaves
**If the post solves your problem, mark it by clicking on "Accept as Solution"
**You can say "Thank you" by clicking the thumbs up in the post that helped you.0 
Ricardo,
Thanks for your reply. I've done this, but i'm still getting the same error. The formula validates, but when i go to make it my yaxis in the graph, it says "An issue has occurred during processing. We are unable to complete the request at this time". It had done this before on the other calculation, but i made the series the total minutes in the month calculation, and that solved the problem. This error still pops even with the same adjustment.
Thanks!
0 
Ok,
So, for debugging sake, will it give you the same error if you lose the null part?
Like this:
((DAYOFMONTH(LAST_DAY(`date_created`))*1440) SUM(TIME_TO_SEC(TIMEDIFF(`time2`,`time1`)))/60) / (DAYOFMONTH(LAST_DAY(`date_created`))*1440)
Regards
Ricardo Granada
MajorDomo@Lusiaves
**If the post solves your problem, mark it by clicking on "Accept as Solution"
**You can say "Thank you" by clicking the thumbs up in the post that helped you.0 
No  the graph will just have no value for the months where no records have values in those date fields. This is making the line graph look like a dot graph ?
0 
If you are sure that `time2` will be null when you have no values try this:
CASE
WHEN IFNULL(`time2`,0) = 0 THEN 1
ELSE ((DAYOFMONTH(LAST_DAY(`date_created`))*1440) SUM(TIME_TO_SEC(TIMEDIFF(`time2`,`time1`)))/60) / (DAYOFMONTH(LAST_DAY(`date_created`))*1440)
ENDTell me how it goes.
Ricardo Granada
MajorDomo@Lusiaves
**If the post solves your problem, mark it by clicking on "Accept as Solution"
**You can say "Thank you" by clicking the thumbs up in the post that helped you.1 
that formula won't validate. now you have me thinking that the value isn't actuall null. would there be a way to throw a trim in here as a 'just in case'?
0 
What is the time2 field data type?
Ricardo Granada
MajorDomo@Lusiaves
**If the post solves your problem, mark it by clicking on "Accept as Solution"
**You can say "Thank you" by clicking the thumbs up in the post that helped you.0 
datetime
0 
Ok,
Datetime can be a little tricky to check for null...
Try it like this:
CASE
WHEN IFNULL(`Date/Time Opened`,'99991231')='99991231' THEN 1
ELSE ((DAYOFMONTH(LAST_DAY(`date_created`))*1440) SUM(TIME_TO_SEC(TIMEDIFF(`time2`,`time1`)))/60) / (DAYOFMONTH(LAST_DAY(`date_created`))*1440)
ENDI tried this on sample data from an excel sheet and it worked with empty DateTime fields.
Tell me how it goes.
Ricardo Granada
MajorDomo@Lusiaves
**If the post solves your problem, mark it by clicking on "Accept as Solution"
**You can say "Thank you" by clicking the thumbs up in the post that helped you.0 
Ricardo,
Thank you for your continued help on this. I'm still getting the same error with the case given most recently. Is it possible that i'm using an improper series for this data which could be causing the error? I used a "total minutes in the month" calculation i made to get it to work with my previous one, but it doesn't seem to like it for this one.
Thanks
0 
The formula doesn't evaluate yet?
Ricardo Granada
MajorDomo@Lusiaves
**If the post solves your problem, mark it by clicking on "Accept as Solution"
**You can say "Thank you" by clicking the thumbs up in the post that helped you.0 
The formula will validate but I get the same issue when adding it to the yaxis of my line graph.
0 
Can you send a screenshot of it or explain it a little deeper...
Regards,
Ricardo Granada
MajorDomo@Lusiaves
**If the post solves your problem, mark it by clicking on "Accept as Solution"
**You can say "Thank you" by clicking the thumbs up in the post that helped you.0 
Certainly  when i drag the new measure that was created with the case statement to the yaxis field in DOMO, the area with the graph goes blank and it throws this error  "An issue has occurred during processing. We are unable to complete the request at this time". Please let me know if you need any more information.
0 
Is it possible for you to send a sample of your data in excel?
Ricardo Granada
MajorDomo@Lusiaves
**If the post solves your problem, mark it by clicking on "Accept as Solution"
**You can say "Thank you" by clicking the thumbs up in the post that helped you.0 
Unfortunately, i cannot due to the sensitivity of the data. There will be a ticket_number field (alpha numeric), creation_date (datetime), start_date (datetime), and finish_date (datetime).
The ticket_number and creation_date will never be null, but the start_date and finish_date will be null sometimes.
Please let me know if there needs to be further explaination.
0 
Here is a picture of my table view that will hopefully help. I'm looking to get the blank values to say 0 and the blank percentages to say 100% for those months.
1
Categories
 All Categories
 1.7K Product Ideas
 1.7K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 292 Workbench
 4 Cloud Amplifier
 8 Federated
 2.8K Transform
 95 SQL DataFlows
 600 Datasets
 2.1K Magic ETL
 3.7K Visualize
 2.4K Charting
 685 Beast Mode
 43 App Studio
 38 Variables
 654 Automate
 169 Apps
 438 APIs & Domo Developer
 42 Workflows
 5 DomoAI
 32 Predict
 12 Jupyter Workspaces
 20 R & Python Tiles
 383 Distribute
 110 Domo Everywhere
 267 Scheduled Reports
 6 Software Integrations
 111 Manage
 108 Governance & Security
 8 Domo University
 25 Product Releases
 Community Forums
 39 Getting Started
 29 Community Member Introductions
 98 Community Announcements
 Domo Community Gallery
 4.8K Archive