How can I merge Creation and Deletion count from two different date series in a pivot?

Hi @ASK -

Currently QuickSight does not allow you to perform a union in data prep. The easiest approach would be to union this data in your ETL.

Assuming that you want to do this 100% in QuickSight, you can use this work around.

Step 1 - Create a modified calendar table to serve as your union.
This will be all possible dates plus a column indicating Table1 (Creation) or Table 2 (Deletion)
image

Step 2 & 3 - Create a calculated field in Table1 and Table2 datasets.
Table
image

Step 4 - Join Table1 and Table2 to your union dataset using your Table calculated field and date.

Step 5 - Create calculated field to count the creation/deletion. Note: you will take the same approach for any value fields you have.
c_count

ifelse(isnull(Table),{Table[table1]},Table)

Result:

Hi @robdhondt,

Thanks for your help!

Small correction in my question, input is already a prepared dataset and have lot of other dependent calculations and visuals on top of the dataset, now I need to create a visual using the below dataset.

Hi @ASK -

Your first question was regarding pivot/union and your second is looking to unpivot. The good news is that unpivot in data prep is “coming soon” see related question: How to unpivot columns in QuickSight? .

Here is a workaround that might get you by in the meantime.

Step 1 - Create a helper dataset “unpivot table”.
This is modified calendar table.
image

Step 2 - Create a calculated field in your dataset as a link.
image

Step 3 - Join your unpivot dataset helper with your dataset using the link field.

Step 4 - Create calculated fields to switch between the unpivoted columns
c_count

ifelse(Action='Creation' AND Date = {Creation Date},1,ifelse(Action='Deletion' AND Date = {Deletion Date},1,0))

Result:

Is it possible to make joins using calculated field? I’m not able to do it.

I have the same issue, not able to find the added link calculated field in the join list