Cumulative active vs total installed?

I have been wracking my brain on this for a few days. I have two tables in a data set joined together on the serial_number field:

scanners
* install_date
* serial_number

scans
* scanner_serial_number
* scan_date

I’m looking to create a visualization that just shows the cumulative installed scanners and the total active scanners (meaning they had one or more scans that month) aggregated by month. I have a cumulative_scanners calculated field that works and can be graphed. The cumulative_scanners calculated field is this:

runningSum
(
    distinct_count(serial_number),
    [{install_date} ASC]
)

That works when I do a vertical bar chart and use the install_date aggregated by month as the x-axis and the cumulative_scanners calculated field as the value. However, I can’t figure out a way to get the active scanners for that month. In order to do that, I’m pretty sure I need to distinct_count the scanner_serial_number aggregated by scan_date, but I can’t use the scan_date and the install_date in the same graph. I tried doing a left join between the tables, which leaves null scanner_serial_number for the joined table for scanners that didn’t do any scans.

I tried creating an active_scanners calculated field like this:

runningSum
(
    distinct_countIf(serial_number, isNotNull({scanner_serial_number})),
    [{scan_date} ASC]
)

but I can’t use that field in the table because scan_date is missing from the table. If I replace the date in that calculated field to be install_date, it works, but it gives me the wrong data – it ends up giving me the total number of active scanners that have ever done any scans, aggregated by their install_date.

I feel like there must be an easy way to do this. In Excel, I just created a pivot table and then made a graph off of that pivot table. I can’t figure out the aggregation in QuickSight. Any ideas???

On your join can you create another date field that checks if either install_date or scan_date is null then return it’s opposite. Call it combine_date and then use that date in your x-axis.

Max is right that you will need to get both of your dates into the same column in order to plot them on the same axis. Another way of doing this would be to UNION your two tables rather than JOINing them. Before the UNION, add a new column to each of your SELECTS that identifies what type of date it is. We want the final result to look like this:

image

Im not a SQL expert but would look something like this:

SELECT
Serial Num as Serial Num,
Install Date as Date,
"Install" as Type
FROM Install Table

UNION ALL

Serial Num as Serial Num,
Scan Date as Date,
"Scan" as Type
FROM Scan Table
1 Like

Thanks so much! That got me headed the right direction!!!

1 Like

Thanks for letting us know @BJamUT! :slight_smile: