Is there a possibility to achieve the result in the picture below?
Briefly, I have a dataset containing an ID, its creation date and resolve date. All IDs have a creation date and a resolved date, but in most cases, an ID is not resolved in the same day.
I want to create a table in QS where I show Inflow (no of IDs by creation date) and Outflow (no of IDs by resolved date) like below.
I tried a few calculations including count, periodOverPeriod, Lag etc but I don’t get the result I want.
Hi @Maria - Since these are in two different columns, we cant ‘smash’ them together into a single axis/field. You would have to do some reshaping of your data. The type of transofrmation is called an Unpivot, which will move the create dates and resolved dates into the same column and introduce a new column which describes what type of date that row is (will result in 2x more records). Then your table uses the combined Date field on Columns, and the new Date Type field on Rows. You can do Unpivots with pretty much any Data Prep tool (in AWS you would use AWS Glue DataBrew.
Other option, which is also a data exercise, would be to create a new table which just has 1 row for each possible date (365 rows per year), then join your data to this new table two times: once on dateCalendar=Create Date and once on dateCalendar=Resolved Date. Then we would use the calendarDate field from your new table in your visual, and add the Distinct Count of ID from your Create Date.ID table and Distinct Count of ID from your Resolved Date.ID table as your metrics.
If you are instead trying to count how many IDs were resolved on the same day vs on different days, or calculated the avg number of days it takes to resolve, you can calculated those with your data as-is.
If you are able to write custom SQL (meaning your data source is something other than an excel/csv file) you can also achieve the same thing as the UNPIVOT using an UNION in SQL. Prob an easier/more direct way of getting your data in the shape needed. Would look something like this:
SELECT
ID as ID,
Create Date as Date,
"Create Date" as Type
FROM YourTable
UNION ALL
ID as ID,
Resolved Date as Date,
"Resolved Date" as Type
FROM YourTable
