Date Ranges based on Calendar rather than action

I am creating a grouped bar chart where I would like to display the count of Action A and count of Action B that happen in any given week.

My issue is that when creating the chart the only option I have is to base the date range on Action A or Action B rather than calendar dates. This causes a problem in that if, for example, I use Action A as my date range and it happened in the previous month I dont get data for Action B happening in this month.

Is there a way for the date range to be based off a calendar so that I get accurate counts for actions/day when setting the date filter to current month (or previous month)?

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Change the way you store your data such that each action has its own row

    date action

    1/1. A

    1/1. B

    1/2 a

    1/3 b

    etc.


    You can utilize the calendar dataset from the Domo Dimensions connector to left join your data set t twice based on the ever a date and based on the event b date and append those two together. You may need to filter and add a constant noting the event type.

    alternatively you may be able to do this with a pivot tile which may be easier depending on how your data is formatted


    Once you have it in that format you can use the single date in your card to track the different events that happen on a day

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Change the way you store your data such that each action has its own row

    date action

    1/1. A

    1/1. B

    1/2 a

    1/3 b

    etc.


    You can utilize the calendar dataset from the Domo Dimensions connector to left join your data set t twice based on the ever a date and based on the event b date and append those two together. You may need to filter and add a constant noting the event type.

    alternatively you may be able to do this with a pivot tile which may be easier depending on how your data is formatted


    Once you have it in that format you can use the single date in your card to track the different events that happen on a day

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • As @GrantSmith said, the surest way to have all calendar dates in your dataset is to join it to the Domo Dimensions calendar dataset.

    Alternatively, if you have an ActionADate and an ActionBDate, you can create a beast mode called date and use this function to ensure it pulls a date:

    IFNULL(ActionADate,ActionBDate)

    Then use this field in your date range filter.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.