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!
- 11.3K All Categories
- 6 Private Company Board
- 2 APAC User Group
- 12 Welcome
- 45 Domo News
- 10.2K Using Domo
- 2K Dataflows
- 2.6K Card Building
- 2.3K Ideas Exchange
- 1.3K Connectors
- 354 Workbench
- 271 Domo Best Practices
- 13 Domo Certification
- 482 Domo Developer
- 58 Domo Everywhere
- 113 Apps
- 734 New to Domo
- 86 Dojo
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 31 お知らせ
- 65 Kowaza
- 305 仲間に相談
- 667 ひらめき共有