CY-PY calculation for a value within a column

Golfoholic
Golfoholic Member
edited April 2021 in Charting

The issue I am running in to is how to call out the value with in the column i want to find CY-PY variance. I am using the formula from Domo page on the topic. In that formula it only has use using a single column. How can I find that variance of one single value with in a column?


SUM(CASE WHEN YEAR(`Activity_Date`) = YEAR(CURRENT_DATE()) THEN `Value` = 'Occ % of Total Area' ELSE 0 END)

- SUM(CASE WHEN YEAR(`Activity_Date`) = YEAR(CURRENT_DATE()) - 1 THEN `Value` = 'Occ % of Total Area' ELSE 0 END)


This is using aggregate data. So I have end of month data historically, with current day. What I would want to is have a calculation I can use for multiple values all located in the value column. YOY - current day % change over same month LY. MTD. and Today's % changed compared to 2 months ago....April 14th compared to february. Attached is sample data.

Sample Data - Variance Calc help.xlsx


Tagged:

Answers

  • GrantSmith
    GrantSmith Coach

    Hi @user048760

    What you're wanting to do is possible by utilizing a date offset dimension. Essentially you'll have records for each day for current, lat week, last month, 2 months ago, whatever you want to define your offset / period definitions. There's been several write ups about this specific topic on the Dojo before. I've written on this topic here: https://dojo.domo.com/discussion/comment/50540#Comment_50540

    Short version:

    • Create a list of dates (use the dates Domo dimension dataset) with offsets (I used a MySQL DataFlow )
    • Join your dataset to this offset table on the date field in your dataset and the comparison date in the offset dataset
    • Use the report date in your reports and filter the offset period type (last week, 2 months ago etc)

    This will allow you compare a single date to whatever time ago you define. You can use beast modes to define your differences over the different periods. For example -

    YoY 

    SUM(CASE WHEN `Offset` = 'Current' THEN `Value` ELSE 0 END) - SUM(CASE WHEN `Offset` = 'Last Year' THEN `Value` ELSE 0 END)

    MoM

    SUM(CASE WHEN `Offset` = 'Current' THEN `Value` ELSE 0 END) - SUM(CASE WHEN `Offset` = 'Last Month' THEN `Value` ELSE 0 END)


    @jaeW_at_Onyx has a good video of this process as well: https://www.youtube.com/watch?v=CDKNOmKClms

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories