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

Find more posts tagged with

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

Tell me how it goes.

availability table view.JPG

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 y-axis 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!

availability table view.JPG

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

availability table view.JPG

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 ?

availability table view.JPG

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

Tell me how it goes.

availability table view.JPG

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'?

availability table view.JPG

What is the time2 field data type?

availability table view.JPG

datetime

availability table view.JPG

Ok,

Datetime can be a little tricky to check for null...

Try it like this:

CASE
WHEN IFNULL(`Date/Time Opened`,'9999-12-31')='9999-12-31' THEN 1
ELSE ((DAYOFMONTH(LAST_DAY(`date_created`))*1440) -SUM(TIME_TO_SEC(TIMEDIFF(`time2`,`time1`)))/60) / (DAYOFMONTH(LAST_DAY(`date_created`))*1440)
END

I tried this on sample data from an excel sheet and it worked with empty DateTime fields.

Tell me how it goes.

availability table view.JPG

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

availability table view.JPG

The formula doesn't evaluate yet?

availability table view.JPG

The formula will validate but I get the same issue when adding it to the y-axis of my line graph.

availability table view.JPG

Can you send a screenshot of it or explain it a little deeper...

Regards,

availability table view.JPG

Certainly - when i drag the new measure that was created with the case statement to the y-axis 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.

availability table view.JPG

Is it possible for you to send a sample of your data in excel?

availability table view.JPG

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.

availability table view.JPG

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.

availability table view.JPG

Quick Links