Magic ETL

Magic ETL

Prior month metrics beast mode

Hi, 

I have a pivot table card which displays Last month projects, Projects 2 Months ago, Delta Projects.

 

Below are the Beast mode calculations:

Last Month Projects

COUNT(DISTINCT CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE) AND MONTH(`Date`) = MONTH(CURRENT_DATE)-1 THEN `ProjectNo` END)

Projects 2 Months ago

COUNT(DISTINCT CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE) AND MONTH(`Date`) = MONTH(CURRENT_DATE)-2 THEN ` ProjectNo` END)

Delta Projects

(COUNT(DISTINCT CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE) AND MONTH(`Date`) = MONTH(CURRENT_DATE)-1 THEN `ProjectNo` END))-

(COUNT(DISTINCT CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE) AND MONTH(`Date`) = MONTH(CURRENT_DATE)-2 THEN `ProjectNo` END))

 

Since we entered into a new year the pivot table shows 0 values for the above fields as the beast mode has YEAR(`Date`) = YEAR(CURRENT_DATE), if I remove YEAR(`Date`) = YEAR(CURRENT_DATE), it will select the years right from the beginning regardless of previous year. I want the card to choose previous year(2020), last month even when we are in current year 2021.

Please advise.

Thanks!

Best Answer

  • Coach
    Answer ✓

    @Khan_Naziya  ? i feel like i've suggested to you multiple times to build a date dimension table... you're not gonna love this answer ?  build a date dimension table.

     

    What you need is a YearMonth_Lag column that calculates the number of month's lag (0,1,2,3,4,5) from the current date.  You calculate that with a Row_Num() Over ( Partition By YearMonth_Num OrderBy End_Of_Month desc)

     

    make sure your window function updates daily.

     

    Then you could simplify your beast mode to 

     

    CASE WHEN YearMonth_Lag = 0  -- current month

    YearMonth_Lag = 1 = prev month

    YearMonth_Lag = 12 = one year ago.

     

    cc @GrantSmith 

    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"

Answers

  • Coach
    Answer ✓

    @Khan_Naziya  ? i feel like i've suggested to you multiple times to build a date dimension table... you're not gonna love this answer ?  build a date dimension table.

     

    What you need is a YearMonth_Lag column that calculates the number of month's lag (0,1,2,3,4,5) from the current date.  You calculate that with a Row_Num() Over ( Partition By YearMonth_Num OrderBy End_Of_Month desc)

     

    make sure your window function updates daily.

     

    Then you could simplify your beast mode to 

     

    CASE WHEN YearMonth_Lag = 0  -- current month

    YearMonth_Lag = 1 = prev month

    YearMonth_Lag = 12 = one year ago.

     

    cc @GrantSmith 

    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"

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In