Period over Period Comparison of Two Date Columns

MichaelClark · 2021-07-21T20:30:51+00:00

There was an error rendering this rich post.

I am using a Period over Period card that's fairly simple.

Gauge Value - Count of Windows OS machines from the asset software (KACE specifically)

Time Period - MAX LastSyncDatePatch (most recent sync date in that column)

My card date Date Range is Month to Date and what I need to compare to is a column called PreviousMonthLastSyncPatch and contains the dates of previous month when synced.

The goal is to count the Windows machines that have synced this month and provide the change from previous month's count of Windows machines that synced.

As always, appreciate the responses in advance!

Michael

Find more posts tagged with

Accepted answers

All comments

ST_Superman

I'm not sure I understand your dataset schema. Since this could impact any potential solution, would you mind providing some more details? I think it would help to have a sample of your dataset.

MichaelClark

Will this work?

MarkSnodgrass

The period over period chart expects a single date column, so you are going to want to restructure your data, or look at a different chart. If you restructured your data like this:

Machine Name DateLastSync

PC1 7/1/2021

PC1 6/1/2021

PC2 6/1/2021

The period over period chart could work for you.

jaeW_at_Onyx

I don't think Period over Period is the right chart type for you.

it sounds like you want to know "how many machines syncced last month"

and "how many machines synced this month"

I don't think you can accurately answer both of those questions with the data provided. // EDIT: I would not structure my data this way.

If it were me I would take a snapshot of a dataset with columns, "snapshot date", "machine name", "last sync" at the end of each month. (just use a recursive dataflow that runs monthly).

Then run a beast mode

-- count syncced current month
sum(case when snapshot_date = <this month> and last_sync = <occurred this month> then 1 end)

-- count syncced last month
sum( case when snapshot_date = <last month> and last_sync = <occured last month> then 1 end)

you can perform math over these two metrics. like variance or percent change etc.