Beast Mode for Summary on Card Needed

user11590

I am not strong with case statements (working on it). How do I build a beast mode for the Summary Section of a card that will display the date range and total dollars.  Below is a beast mode we created for our report (card) to display results, I just cant get the date range and figures to show in the Summary.

 

CASE WHEN
DAY(CURDATE()) >= 1 AND DAY(CURDATE()) <= 18 THEN
Sum(case when YEAR(`Date`) = YEAR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH)) and DATE_FORMAT(`DATE`,'%m') = MONTH(DATE_SUB(CURDATE(),INTERVAL 1 MONTH))
then `Total Retail Change $` End)
else
Sum(case when YEAR(`Date`) = YEAR(CURDATE()) and DATE_FORMAT(`DATE`,'%m') = MONTH(CURDATE())
then `Total Retail Change $` End)
end

 

Any help is greatly appreciated.

 

Neeti

Hi @user11590 

 

I dint understand the statement and the condition provided by you in below case.

I could see `Total Retail Change $`  is same in both the condition.

 

please repharase is cleary, so that I can try to help you on this.

 

Below case for you refrence:

SUM(CASE WHEN `transaction_type` = 'Actuals' AND
(
( YEAR(`transaction_date`) = YEAR(CURRENT_DATE()) AND MONTH(`transaction_date`) < 7 ) OR
( YEAR(`transaction_date`) = YEAR(CURRENT_DATE())-1 AND MONTH(`transaction_date`) >= 7 )
)
THEN
`net`
ELSE 0
END)

 

Thanks,

Neeti

 

Please do like if you are happy with my reply.

GrantSmith

@user11590 - You could utilize CONCAT to combine the different data elements.

 

Note: The following code is untested.

 

I'm utilizing your same logic for the dates to only display the dates where it's matching the logic of the revenue $

CONCAT(
MIN(
  CASE WHEN DAY(CURDATE()) >= 1 AND DAY(CURDATE()) <= 18 THEN
     CASE WHEN YEAR(`Date`) = YEAR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH)) and DATE_FORMAT(`DATE`,'%m') = MONTH(DATE_SUB(CURDATE(),INTERVAL 1 MONTH)) then
          `DATE`
      END
  ELSE
      Sum(case when YEAR(`Date`) = YEAR(CURDATE()) and DATE_FORMAT(`DATE`,'%m') = MONTH(CURDATE()) 
  THEN
       `DATE`
  END)
  end
),
'-'
,MAX(
  CASE WHEN DAY(CURDATE()) >= 1 AND DAY(CURDATE()) <= 18 THEN
     CASE WHEN YEAR(`Date`) = YEAR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH)) and DATE_FORMAT(`DATE`,'%m') = MONTH(DATE_SUB(CURDATE(),INTERVAL 1 MONTH)) then
           `DATE`
      END
  ELSE
      Sum(case when YEAR(`Date`) = YEAR(CURDATE()) and DATE_FORMAT(`DATE`,'%m') = MONTH(CURDATE()) 
  THEN
        `DATE`
  END)
  end
),
, ' '
SUM(
  CASE WHEN DAY(CURDATE()) >= 1 AND DAY(CURDATE()) <= 18 THEN
     CASE WHEN YEAR(`Date`) = YEAR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH)) and DATE_FORMAT(`DATE`,'%m') = MONTH(DATE_SUB(CURDATE(),INTERVAL 1 MONTH)) then
           `Total Retail Change $`
      END
  ELSE
      Sum(case when YEAR(`Date`) = YEAR(CURDATE()) and DATE_FORMAT(`DATE`,'%m') = MONTH(CURDATE()) 
  THEN
        `Total Retail Change $`
  END)
  end
)

)

 

The downside to this is that your sum $ amount won't be formatted as a nice currency anymore. There are additional case statements that you can utilize to format it properly but make for very ugly code currently until Domo decides to allow SQL formatting functions in their beast modes.

What I would recommend is creating three separate Beast Modes to make testing easier. One for the first date, one for the last date and one for the total amount. Once those have been written and verified they're correct you can create the fourth summary beast mode and just do:

CONCAT(
[INSERT MIN DATE BEASTMODE HERE]
,' - '
, [INSERT MAX DATE BEASTMODE HERE]
, ' '
, [INSERT $ BEAST MODE HERE]
)

When you insert it it will expand the code into your summary number beast mode that was already written and tested.

 

 

 

An example Beast Mode to display $ amounts in an abbreviated form (handles correclty up to numbers < $1T)

CONCAT('$',CASE WHEN LENGTH(ROUND(SUM(`Total`), 0)) >= 10 THEN
        CONCAT(
          ROUND(SUM(`Total`) / 1000000000, 2), 'B'
    )
WHEN LENGTH(ROUND(SUM(`Total`), 0)) >= 7 THEN
        CONCAT(
          ROUND(SUM(`Total`) / 1000000, 2), 'M'
    )
WHEN LENGTH(ROUND(SUM(`Total`), 0)) >= 4 THEN
        CONCAT(
          ROUND(SUM(`Total`) / 1000000, 2), 'K'
    )
WHEN LENGTH(ROUND(SUM(`Total`), 0)) <= 3 THEN
      ROUND(SUM(`Total`), 0)
WHEN SUM(`Total`) IS NULL THEN 0
ELSE 'ERROR'
END)

With you example though you'd want to replace SUM(`Total`) with your beast mode to calculate the total $ amount.