Multiple Values Across Columns Compared Over Last Year

Hi everyone,

 

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:

Column1Column2Column3Column4
Identifier-1232018DuckGoose
Identifier-1232018DuckGoose
Identifier-4562018Horse 
Identifier-7892018Unrelated Information
Identifier-7892018Unrelated Information
Identifier-0122017Unrelated InformationDuck

 

The resulting output I expect to see:

 

Duck0
Goose1
Horse1

 

(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!

Tagged:

Comments

  • Aggregations inside of beast modes are really problematic.  There are a few threads out there in Dojo that go into more depth on this topic.  To solve it you essentially have to turn the beast modes inside out.  Instead of "if it equals X, count it" it goes more like "count it if it equals X".

     

    Count of Ducks this year would look something like this:

    COUNT(DISTINCT CASE WHEN `column2` = year(current_date()) AND (`column3` = 'Duck' OR `column4` = 'Duck')  THEN `column1` END)

     

    Use similar logic for last year, and/or combine the two into one beast mode to get the difference.

     

    COUNT(DISTINCT CASE WHEN `column2` = year(current_date()) AND (`column3` = 'Duck' OR `column4` = 'Duck')  THEN `column1` END)

    -

    COUNT(DISTINCT CASE WHEN `column2` = year(current_date())-1 AND (`column3` = 'Duck' OR `column4` = 'Duck')  THEN `column1` END)