How to edit DATEDIFF to calculate beyond calendar year

Aaron_w_SMG
Aaron_w_SMG Member
in Beast Mode

Hi, I have an argument where I am attempting to calculate how many consecutive days have occurred since the last specific event (See pic below). But when the 'Year' filter is applied to the dashboard and also filtered by a 'Region' and the event occurred in the prior year, I get a "no data in filtered range". Is there a way to amend the argument where it doesn't default date to the current year, that way if the injury occurred in the prior year it will display the correct number of days?

When the last event occurred in this calendar year the card displays the correct result. Example: If 'Region' had an event on 12/12/22 I get the above error. But if the 'Region' had an event on 1/1/23 the card will display the correct number of days.

image.png

Thank you for all the guidance,

Tagged:

Best Answer

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    So you're not using the actual date controls from Domo but a filter card instead, that is why the option to hide it won't work. What worries me is that you say that applying those same filters while editing the card does show results, that sounds like a really weird behavior and something for which you might need to reach out to support about, as that inconsistency in behavior shouldn't be happening.

    1. The "Controls" you have at the top of the page, are those variables or filter cards?
    2. If you apply only one of the two filters, say only the department, does the card work and shows you the right number?
    3. Have you tried adding the Year and Department fields as quick filters to the card and filtering the card with those instead?

    If that Year field is predicated from the Incident table, and as you mentioned a department has no incidents in the year, getting the no Data In Range would make sense.

    If they are Filter Cards, you might be able to get around by updating the dashboard and configuring the interaction on the Year card not to affect all the cards but the one were you show time since last incident, if it's a variable, then you'll need to see how the setting of that variable is being applied on your beastmodes on that card. Just an idea that might work.

    image.png
    image.png

Answers

  • MichelleH
    MichelleH Coach

    @Aaron_w_SMG Is the Year filter based on the IncidentDateTime or is it derived from a different date field? Is it possible that there are conflicting date filters in your cards that result in all the data being filtered out?

  • Aaron_w_SMG
    Aaron_w_SMG Member

    Hi Michelle,

    In the dashboard, the 'Year' is filtered by 'Year' column within the data.

  • MichelleH
    MichelleH Coach

    @Aaron_w_SMG Do your cards already have a timeframe set in the Date Range settings (i.e. Year to Date)?

  • Aaron_w_SMG
    Aaron_w_SMG Member

    @MichelleH all cards "Date Range" is set to "All Time". As my data covers inputs from 2012 and I use filters to trim the results of the dashboard.

  • MichelleH
    MichelleH Coach

    @Aaron_w_SMG Have you validated that all there are records in your data that meet the criteria for all the filters?

  • marcel_luthi
    marcel_luthi Coach
    edited July 2023

    @Aaron_w_SMG when you say you use filters, do you mean you're applying a DateRange at the Dashboard/Card level?

    image.png

    If the idea of the card is to show time since the last incident, regardless of when it happened, you'd need to make it so that the card itself ignores the dashboard date filter by selecting the option to hide it from card details:

    image.png

    I guess it depends on what the final visualization and the question to be answered is.

    PD: The MIN around CURDATE is redundant and to make it easier for anyone to understand, in case you need to look at this months from now, you might want to rewrite the MIN(CURDATE()) - 1 as CURDATE() - INTERVAL 1 DAY so it's more self explanatory (lesson I've learned the hard way).

  • Aaron_w_SMG
    Aaron_w_SMG Member

    @MichelleH good morning, I have checked that there is data when the card is filtered in the Analyzer. When in the analyzer the card displays the correct result when filtered in the analyzer.

    @marcel_luthi good morning, I attempted your suggestion and I still get the same result when filtered on the dashboard. Below is a screenshot of the dashboard filters that I am applying. When I'm in the card using the analyzer and using filters there I get the correct result.

    image.png

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    So you're not using the actual date controls from Domo but a filter card instead, that is why the option to hide it won't work. What worries me is that you say that applying those same filters while editing the card does show results, that sounds like a really weird behavior and something for which you might need to reach out to support about, as that inconsistency in behavior shouldn't be happening.

    1. The "Controls" you have at the top of the page, are those variables or filter cards?
    2. If you apply only one of the two filters, say only the department, does the card work and shows you the right number?
    3. Have you tried adding the Year and Department fields as quick filters to the card and filtering the card with those instead?

    If that Year field is predicated from the Incident table, and as you mentioned a department has no incidents in the year, getting the no Data In Range would make sense.

    If they are Filter Cards, you might be able to get around by updating the dashboard and configuring the interaction on the Year card not to affect all the cards but the one were you show time since last incident, if it's a variable, then you'll need to see how the setting of that variable is being applied on your beastmodes on that card. Just an idea that might work.

    image.png
    image.png

Categories