Need help with beast modes to show yesterday's invoice total amount, and same day last year

anh_tr_huynh4
anh_tr_huynh4 Member
edited November 2022 in Beast Mode

I'm trying to create 2 beast modes to show yesterday's Total Invoice Amount and same day last year's Total Invoice Amount. For example, 10/27/2022, and 10/28/21

Yesterday: case when year(`INVOICE_DATE`) = year(CURDATE()) and MONTH(`INVOICE_DATE`) = MONTH(CURDATE()) then `INVOICE_TOTAL_AMOUNT` end --- This formula works

Same day last year: case when year(`INVOICE_DATE`)=YEAR(CURDATE()) -1 and YEAR(INVOICE_DATE) = MONTH(CURDATE()) then INVOICE_TOTAL_AMOUNT

Same day last year's formula/code does not work.

Please help me. Thank you so much

image.png


Analyzer.PNG


Tagged:

Answers

  • Billobi
    Billobi Contributor

    Watch out for your second portion of the evaluation...easy typo but you're looking at YEAR() vs MONTH() so it's not going to yield any results.

    Same day last year: case when year(`INVOICE_DATE`)=YEAR(CURDATE()) -1 and YEAR(INVOICE_DATE) = MONTH(CURDATE()) then INVOICE_TOTAL_AMOUNT

    ^^^ instead of year(invoice_date) I think you'd want month(invoice_date)

    Your formulas don't really sync up with how you're describing the need. The "yesterday" beast shown is more like "current month to-date, including today". Yesterday would be something along the lines of case when INVOICE_DATE=CURDATE()-1 then INVOICE_TOTAL_AMOUNT.

    When talking about yesterday year over year you can do it many ways. Keep leap years in mind so you're ready for 2024. Using DAYOFYEAR() is really easy and will work fine till 2024, then it's not a perfect calendar day match for most months. Or could use something along the lines of WHEN DATE_SUB(CURDATE(),INTERVAL 366 DAY) = INVOICE_DATE THEN INVOICE_TOTAL_AMOUNT. Or something that looks at DAY/MONTH/YEAR-1 matches. So many ways... :)

  • anh_tr_huynh4
    anh_tr_huynh4 Member

    Hi Bilobi, thank you for your comment. Let me explain again so you can understand better. My grammar is bad. Sorry. What I'm trying to do is I'm creating a daily ecommerce report, that shows previous day's report vs same day previous year's report. This sentence is really confusing. For example, yesterday Thursday was 10/27/22 and last year was Thursday 10/28/21 (+1 day). Because I selected the date range as "Yesterday" (see top right corner), below picture is showing invoice total amount for yesterday. What i want to do is i want to create a beast mode that can show invoice total amount same day last year (which is 10/28/21), how can i do that? Please help

    Formula for yesterday Invoice total amount is case when year(`INVOICE_DATE`) = year(CURDATE()) and MONTH(`INVOICE_DATE`) = MONTH(CURDATE()) then `INVOICE_TOTAL_AMOUNT`end

    As you can see, this yesterday beast mode matches the invoice_total_amount ( I used InvoiceTotal_Amount as Measures and I sum it. The numbers match


    Formula for same day last year Total:

    case when YEAR(`INVOICE_DATE`) =YEAR(CURDATE()) -1 and MONTH(`INVOICE_DATE`) = MONTH(CURDATE()) then `INVOICE_TOTAL_AMOUNT` END

    Same day last year is not showing any numbers. I don't know what to do.

    image.png
    image.png


Categories