Period over Period help

Jones01
Jones01 Contributor
edited October 2022 in Magic ETL

@GrantSmith

Hi Grant, I have used your date dimension solution for lots of PoP charts but like you have mentioned on your post doing month over month can be tricky. Thanks for your help so far on my domo journey.

My problem. I have daily data and have a period type of 'Last Month' and 'This Month'

I have the data in a pivot table with months along the top.

I have noticed for October when showing "Last Month" it is including 30th September twice.

Is there a sensible way around this?

Thanks

Answers

  • Jones01
    Jones01 Contributor

    @GrantSmith I believe I have found a solution by rewriting the comparison date with a formula in my ETL.

    case when `Period Type` = 'LM' and DAYOFMONTH(`Report Date`) != DAYOFMONTH(`Comparison Date`) then ADDDATE(`Comparison Date`, 36500) else `Comparison Date` end

    I only have data for 1 month so difficult to test properly. Do you think this is a reasonable workaround?

Categories