Multiple Values Across Columns Compared Over Last Year
I have two columns of data with similar values, e.g., Duck, Goose, Horse, etc. I am agnostic to which column it is in, I would like to know the net change over the previous year (preferably in a heat map table).
Here is an example of what the data may look like:
The resulting output I expect to see:
(Duck ends up being 0 because 1 in current year minus 1 in previous year, Goose is 1 because the rows of data duplicate)
What I have tried so far is creating a calculation similar to this:
(CASE WHEN `Column3`='Duck' THEN COUNT(DISTINCT `Column1`)+COUNT(DISTINCT (CASE WHEN `Column3`<>'Duck' AND `Column4`='Duck' THEN`Column1` end))
This works great initially however it breaks down when I start to throw dates to see a year over year comparison. I've tried this:
(CASE WHEN `Column3`='Duck' AND `Column2`='2018' THEN COUNT(DISTINCT `Column1`)+COUNT(DISTINCT (CASE WHEN `Column3`<>'Duck' AND `Column4`='Duck' AND `Column2`='2018' THEN `Column1` end))-(CASE WHEN `Column3`='Duck' AND `Column2`='2017' THEN COUNT(DISTINCT `Column1`)-COUNT(DISTINCT (CASE WHEN `Column3`<>'Duck' AND `Column4`='Duck' AND `Column2`='2017' THEN `Column1` end))
The calculation appears to be valid but the resulting visualization fails to load unless I list Column2 as a displayed column which breaks aggregation.
Any recommendations on how I can approach this? Expressions or operators I should look into?
In short: I need to know how many unique identifiers contained 'Ducks' this year and last year in either column and find the difference.
Thank you for any time anyone can spare, I appreciate any help or advice!
- 10.6K All Categories
- 13 Getting Started in the Community
- 29 Beastmode & Analytics
- 2.1K Data Platform & Data Science
- 59 Domo Everywhere
- 2.7K Charting
- 2.4K Ideas Exchange
- 1.3K Connectors
- 362 Workbench
- 300 Use Cases & Best Practices
- 499 APIs
- 118 Apps
- 48 News
- 753 Onboarding
- 1.1K 日本支部
- 4 Private Company Board