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:

Identifier-7892018Unrelated Information
Identifier-7892018Unrelated Information
Identifier-0122017Unrelated InformationDuck


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!



  • AS

    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)

    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"