Using case statement to make a value if null and run a calculation if not

user07322
user07322 Member
in Archive

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

  • RGranada
    RGranada Contributor

    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.

     

    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.
  • user07322
    user07322 Member

    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!

     

  • RGranada
    RGranada Contributor

    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.
  • user07322
    user07322 Member

    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 ?

     

  • RGranada
    RGranada Contributor

    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.

    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.
  • user07322
    user07322 Member

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

  • RGranada
    RGranada Contributor

    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.
  • user07322
    user07322 Member

    datetime

  • RGranada
    RGranada Contributor

    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.

    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.
  • user07322
    user07322 Member

    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

  • RGranada
    RGranada Contributor

     

    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.
  • user07322
    user07322 Member

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

  • RGranada
    RGranada Contributor

    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.
  • user07322
    user07322 Member

    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.

  • RGranada
    RGranada Contributor

    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.
  • user07322
    user07322 Member

    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.

  • user07322
    user07322 Member

    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.

Categories