Skip to content

    • Beast Mode

    Beast Mode

    Calculating a % based on multiple dates

    Member
    in Beast Mode

    I would like to create a beast mode that calculates the % of the way we are into a campaign. In Excel, I would calculate this as =(TODAY()-'Start Date') / ('End Date' - 'Start Date')

    Here's what I have so far (I want this to return a %):

    (SUM(IFNULL(CURDATE(), 0) - IFNULL(`Start Date`, 0)) / NULLIF(SUM(IFNULL(`End Date`, 0) - IFNULL(`Start Date`, 0)), 0))

    The formula validates fine but I get an error when I add it to a card.

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answers

    • Coach
      edited January 2024 Answer ✓

      A couple of questions:

      1. What is the answer you would expect to get when a Campaign has no start date?
      2. What should be the answer when a campaign has no end date?

      In your dataset, what is the level of granularity you have? If this is a campaign level dataset, then a formula to cover the out of bond scenarios (no start date, not yet started, no end date and already ended) first, and then operate at the row level is likely best:

      1. CASE
      2. WHEN `StartDate` IS NULL THEN 0
      3. WHEN `StartDate` > CURDATE() THEN 0
      4. WHEN `EndDate` IS NULL THEN 0
      5. WHEN `EndDate` < CURDATE() THEN 1
      6. ELSE ROUND(((UNIX_TIMESTAMP(CURDATE())-UNIX_TIMESTAMP(`StartDate`))/86400)-0.5,0)/ROUND(((UNIX_TIMESTAMP(`EndDate`)-UNIX_TIMESTAMP(`StartDate`))/86400)-0.5,0) 
      7. END

      You could try with DATEDIFF but that one is known to behave oddly sometimes, so the UNIX diff is often preferred and more reliable, feel free to try both.

    • Domo Employee
      Answer ✓

      You could add a case statement

      Case when curdate() > End Date then 1 else … end

      where the … is your formula from above

    Answers

    • Member

      SUM(IFNULL(DATEDIFF(CURDATE(), 'Start Date'), 0)) / NULLIF(SUM(IFNULL(DATEDIFF('End Date', 'Start Date'), 0)), 0)

    • Coach
      edited January 2024 Answer ✓

      A couple of questions:

      1. What is the answer you would expect to get when a Campaign has no start date?
      2. What should be the answer when a campaign has no end date?

      In your dataset, what is the level of granularity you have? If this is a campaign level dataset, then a formula to cover the out of bond scenarios (no start date, not yet started, no end date and already ended) first, and then operate at the row level is likely best:

      1. CASE
      2. WHEN `StartDate` IS NULL THEN 0
      3. WHEN `StartDate` > CURDATE() THEN 0
      4. WHEN `EndDate` IS NULL THEN 0
      5. WHEN `EndDate` < CURDATE() THEN 1
      6. ELSE ROUND(((UNIX_TIMESTAMP(CURDATE())-UNIX_TIMESTAMP(`StartDate`))/86400)-0.5,0)/ROUND(((UNIX_TIMESTAMP(`EndDate`)-UNIX_TIMESTAMP(`StartDate`))/86400)-0.5,0) 
      7. END

      You could try with DATEDIFF but that one is known to behave oddly sometimes, so the UNIX diff is often preferred and more reliable, feel free to try both.

    • Member

      Thanks for all the replies. I ended up going with:

      DATEDIFF(CURDATE(), Start Date) / DATEDIFF(End Date, Start Date)

      I would like to cap the results at 100%, can I get help with that?

    • Domo Employee
      Answer ✓

      You could add a case statement

      Case when curdate() > End Date then 1 else … end

      where the … is your formula from above

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories