How to show a unique count for a different period than the selected date range?
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!
Best Answers
-
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:
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
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:
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
In case you want to use your beast mode:
COUNTDISTINCT(CASE WHEN
Reporting Date
= '31 December 2023' THENClient 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"1
Answers
-
Can you provide a sample of your data and a screenshot of the card you are trying to create?
0 -
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:
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
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:
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
In case you want to use your beast mode:
COUNTDISTINCT(CASE WHEN
Reporting Date
= '31 December 2023' THENClient 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"1
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 50 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 116 Manage
- 113 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive