Dual figures in the Summary Field with CONCAT

IVG
IVG Member
in Magic ETL

So i found the solution to get two different values in the summary field with CONCAT command. I have the following beast mode command

CONCAT('$', round(sum(`Value`), 2), '  Period Total    |   ','$', max(Case when (`date` = CURRENT_DATE()) then round(`Value`, 2) end), ' Today')

what it does is allows me to se a sum of values in the selected timerange AND the last day value. The problem is it works perfectly when I select any current timeperiod from the date selector dropdown, however if I select any previous period (previous month, day, year) I get the result "0"....

 

What am I doing wrong!? please help!

 

 

 

Best Answer

  • ST_-Superman-_
    ST_-Superman-_ Coach
    Answer ✓

    I think that this has to do with you when statement on your "today" part of the beast mode.

     

    Try this:

    CONCAT('$', round(sum(`Value`), 2), '  Period Total    |   ','$', ifnull(max(Case when (`date` = CURRENT_DATE()) then round(`Value`, 2) end),0), ' Today')

    But know that if the period that you select does not include today, then it will show 0 for "Today"

     


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman

Answers

  • ST_-Superman-_
    ST_-Superman-_ Coach
    Answer ✓

    I think that this has to do with you when statement on your "today" part of the beast mode.

     

    Try this:

    CONCAT('$', round(sum(`Value`), 2), '  Period Total    |   ','$', ifnull(max(Case when (`date` = CURRENT_DATE()) then round(`Value`, 2) end),0), ' Today')

    But know that if the period that you select does not include today, then it will show 0 for "Today"

     


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • IVG
    IVG Member

    Thank you! this is a better approach that I had because in my command if I use a period that does not include today then the entire Summary Value is 0 but with your approach it only zero's out the today's value...

     

    If I understand correctly that has to do with how DOMO looks at data sets and that selecting a date range effectively eliminates all values outside of that range for the purposes of Beast Mode Command... which is a strange way of doing it, but when you simply select the current value in the summary field it will find it regardless of the data range you select.... it seems like it is an oversight on DOMO's part and I can't help but wander if there is a workaround for it...

     

    In any event thank you for your solution!

Categories