Prior month metrics beast mode

Khan
Khan Member
in Magic ETL

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

  • jaeW_at_Onyx
    jaeW_at_Onyx 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

  • jaeW_at_Onyx
    jaeW_at_Onyx 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"

Categories