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.


    ______________________________________________________________________________________________
    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
    ______________________________________________________________________________________________
  • 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.

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤
    edited July 2021

    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.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"