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
**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
**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
**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
**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
**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
**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
**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
**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
 10.5K All Categories
 6 Connect
 916 Connectors
 250 Workbench
 460 Transform
 1.7K Magic ETL
 69 SQL DataFlows
 476 Datasets
 187 Visualize
 251 Beast Mode
 2.1K Charting
 11 Variables
 16 Automate
 354 APIs & Domo Developer
 88 Apps
 3 Workflows
 20 Predict
 5 Jupyter Workspaces
 15 R & Python Tiles
 245 Distribute
 62 Domo Everywhere
 242 Scheduled Reports
 21 Manage
 42 Governance & Security
 170 Product Ideas
 1.2K Ideas Exchange
 10 Community Forums
 27 Getting Started
 14 Community Member Introductions
 55 Community News
 4.5K Archive