Grouping dates for multiple date columns

AMPatel
AMPatel ⚪️
edited January 4 in Charting

Hi all,

I have a table with the following structure:

Name of Asset   Date_Purchased  Date_Registered   Date_Issued
Asset 1         12/01/2020      21/02/2020        25/02/2020
Asset 2         12/01/2020      21/02/2020        26/02/2020
Asset 3         13/01/2020      23/02/2020        27/02/2020
...

I'd like restructure this based on a independent date column, that has all the days of the year increasing by 1 day for each row, and then count the relevant dates agains that.

   
Date            Count_Purchased  Count_Registered   Count_Issued
11/01/2020      0                0                   2
12/01/2020      2                0                   2
...
21/02/2020      0                2                   0
...


Currently when I group in analyser, I can only anchor the data column by the Date_purchased, Date Registered OR Date Issued (unless i'm using it wrong).

Been watching videos and reading up, but can't seem to find out how to even do this as an ETL Transform.

Tagged:

Categories