How to show a unique count for a different period than the selected date range?

Kyle_Russell
Kyle_Russell Member
in Cards, Dashboards, Stories

Hi All,

I have a card where I am showing a year to date breakdown of assets, in order to show this I have 'This Year' selected for my date range. However ideally I want to show a count of unique accounts as at the end of the prior year, a count of unique accounts as at the end of the current month and the difference between these. I have tried doing this via the below beast mode:

CASE WHEN 'Reporting Date' = '31 December 2023' THEN COUNTDISTINCT(Client Entity ID) END.

However this seems to be pulling through blank values and I'm not sure where I'm going wrong, if anyone could help it would be much appreciated!

Tagged:

Best Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    When you set the date range filter to This Year, you eliminate any previous year data. You would need to include the last 2 years in your date range filter and then use a beast mode to filter out the dates you don't want and add that to your filters.

    This video may help you:

    https://youtu.be/qxhV3gAZnk4

    **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.
  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Another alternative is to re-architect your data so that you have different offsets for your different dates. This way you can select the current year but show the current year, last year or any other period you've defined. I've done a write up on this in the past here:

    https://community-forums.domo.com/main/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • DataMaven
    DataMaven Coach
    edited February 28 Answer ✓

    In case you want to use your beast mode:

    COUNTDISTINCT(CASE WHEN Reporting Date = '31 December 2023' THEN Client Entity ID END)

    Obviously, that's not exactly it, given relative dates, etc, but I wanted to give you the order for that type of beast mode in case it helps you.

    DataMaven
    Breaking Down Silos - Building Bridges
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • jrudd
    jrudd Member

    Can you provide a sample of your data and a screenshot of the card you are trying to create?

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    When you set the date range filter to This Year, you eliminate any previous year data. You would need to include the last 2 years in your date range filter and then use a beast mode to filter out the dates you don't want and add that to your filters.

    This video may help you:

    https://youtu.be/qxhV3gAZnk4

    **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.
  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Another alternative is to re-architect your data so that you have different offsets for your different dates. This way you can select the current year but show the current year, last year or any other period you've defined. I've done a write up on this in the past here:

    https://community-forums.domo.com/main/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • DataMaven
    DataMaven Coach
    edited February 28 Answer ✓

    In case you want to use your beast mode:

    COUNTDISTINCT(CASE WHEN Reporting Date = '31 December 2023' THEN Client Entity ID END)

    Obviously, that's not exactly it, given relative dates, etc, but I wanted to give you the order for that type of beast mode in case it helps you.

    DataMaven
    Breaking Down Silos - Building Bridges
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories