Period over Period Comparison of Two Date Columns

MichaelClark
MichaelClark Wichita Falls, Texas ⚪️

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

Answers

  • 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
    MichaelClark Wichita Falls, Texas ⚪️

    Will this work?

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    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
    jaeW_at_Onyx Budapest / Portland, OR 🟤
    edited July 22

    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.