I am using the KPI visual in quicksight to show the change in a calculated field from one month to the next.
My data is transactional data. Each record in the db includes a "transaction date" and the total dollar amount for the transaction. In the month of December 2021, we recorded no transactions in the database. I am using the transaction date field from this database to drive time based aggregations of the total number of tranasctions and the total value of transactions over a period. I'm also using that date field to drive this KPI visual.
Problem is - my data doesn't have anything for decemeber, so that month doesn't exist in the aggregated result and therefore doesn't show up in my KPI visual.
I was able to get the gap in the timeline to show up with a column chart over time, but not the KPI.
Is there a data analysis concept I don't know about here where time is handled as an independant dimension outside of the dataset?
Usually you would have a date dimension table that contains dates (with the granularity you need for your dashboards).
You would then LEFT JOIN this date dimension table to your table containing the transactional data on the date field. This will make sure that all your transactions are included and if there are not transactions for some particular date, then the left join still includes a row for that date but without any of the fields from your transaction table being populated.