Beast Mode Summary Number Total Count for Data 12 months ago

I am using period over period card.  I would like to be able to show in the summary the total "lifts" for the current month last year.  I'm going to concat that with the sum of the current months lifts.  But I can't quite figure out how to get the beast mode to capture only lifts from 12 months ago.  Field name = "date" and "lifts"  should be all I need to use.  

I was able to get a sum statement to work'ish.  But when I put it in the Summary, it doesn't give me data only a "NO". 

case
when
year(`route date`) = year(current_date()) - 1
and month(current_date()) = month(`route date`)
then
SUM(`Lifts`) ELSE 'NO' END

 

domo.png

Best Answer

  • ST_-Superman-_
    Answer ✓

    I created a data set with two fields for this; `route date` (date field) and `Lifts` (integer)

     

    I then created a few beastmodes to engineer a few more fields:

     

    -Route Month: This allows me to see the month name in the flex table card

    MONTHNAME(`route date`)

    -Route Year: This is for the series on my running total graph (I used concat to make sure it ended as a text value and not an integer).  Another note for this field, don't use a name like `Year` for a calculated field.  That name is reserved in some of the back end processes and while it won't break your card, it can cause some strange things to happen from time to time, best to describe it a little more `Route Year`

    concat(YEAR(`route date`))

    -Day of Month: This is for the running total graph as well (x-axis)

    concat(DAYOFMONTH(`route date`))

    Next I had to create a few more beastmodes to use as filters.

    -current month filter:  I use this to allow you to view the current month or previous month.  I find that with most MTD cards, the end users immediately want to see the prior month when the calendar flips over.  With this filter, you can just change it from 'Current Month' to 'last month'

    case when month(`route date`) = MONTH(CURDATE()) then 'current month'
    when MONTH(`route date`) = MONTH(CURDATE())-1 then 'last month'
    when MONTH(CURDATE())=1 and MONTH(`route date`)=12 then 'last month'
    else 'other'
    end

    -YTD filter:  This is used for the flex table because I didn't want to compare 10 days of April this year to a full month last year.  I added a few extra lines to allow for multiple lines.  The data set that I created only went back one year, but this will allow you to show up to 3 years ago (add more if needed)

    case when `route date`<= CURDATE() and YEAR(`route date`) = YEAR(CURDATE()) then 'YTD'
    when `route date`<= DATE_SUB(CURDATE(),interval 1 year) and YEAR(`route date`) = YEAR(CURDATE())-1 then 'YTD'
    when `route date`<= DATE_SUB(CURDATE(),interval 2 year) and YEAR(`route date`) = YEAR(CURDATE())-2 then 'YTD'
    when `route date`<= DATE_SUB(CURDATE(),interval 3 year) and YEAR(`route date`) = YEAR(CURDATE())-3 then 'YTD'
    end

    With all of those fields made (and saved to data set) you are ready to build your cards.

     

    I'll start with the flex table card.  This is going to "replace" your summary number.  This card type has a bit of a learning curve because the bulk of the settings are handled under the general tab.  Also, I filtered the card to only show YTD and Current Month valuesFlex Tables- Two thumbs up!Flex Tables- Two thumbs up!

     

    How to set up the axisHow to set up the axis

     

    General SettingsGeneral Settings

    Also, I prefer to look at most MTD metrics as a running total.  This is because in my business it is not as important to know day over day, but more how things end up at the end of the month.  For example, it does not matter to me if a large sale came in on the 2nd last year but not until the 12th this year, as long as the total for the month is comparable.  You may want to change this visualization based on your needs.4.png

     

     

    Now that our cards are created, make sure they are saved to the same page and lets create a story.

    https://youtu.be/EwCKZ_XfDlE

     


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

Answers

  • Canioagain
    Canioagain Contributor

    I should also say I was able to get this to work, but I can't seem to get it to work on the summary line and show results from April 2018.  It just says, "NO" for me

    case
    when
    year(`route date`) = year(current_date()) - 1
    and month(current_date()) = month(`route date`)
    then
    SUM(`Lifts`) ELSE 'NO' END

    ,  

  • Try this

     

    Sum(case
    when
    year(`route date`) = year(current_date()) - 1
    and month(current_date()) = month(`route date`)
    then
    SUM(`Lifts`) ELSE 0 END)

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

     

    Sum(case
    when
    year(`route date`) = year(current_date()) - 1
    and month(current_date()) = month(`route date`)
    then
    `Lifts` ELSE 0 END)

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

    So with the following beast mode it doesn't exactly work in the Summary b/c this is a Period over Time Card I won't have the dates for last year.  What I need to do, and can't wrap my brain around is how to say;   When the Route Date = this year and month, then give me the Lifts from this same month Last Year

    concat( 'Current MTD =', sum(`Lifts`), ' Last Year =',        

    Sum(case
    when
    year(`route date`) = year(current_date()) - 1
    and month(current_date()) = month(`route date`)
    then `Lifts` ELSE 0 END))

    domo.png

     

    Below is what I need to figure out how to code, I think

    concat( 'Current MTD =', sum(`Lifts`), ' Last Year =',        

    Sum(case when year(`route date`) = year(current_date())
    and month(current_date()) = month(`route date`)

    then

    Sum the lifts where Year(CURRENT_DATE()-365) ELSE 0 END))
  • I'm not sure why we are so hyper focused on getting the summary number to do such heavy lifting here.  I think that we could leverage stories to bring together a flex table and a running total graph to look something like this:1.png

    Would this meet your needs?  If so I can try to walk through how to build it


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

    well, I didn't think it would be heavy lifting is why ?

    Our Operating guy wanted it, so I thought, "how hard can it be"

  • Canioagain
    Canioagain Contributor

    I'm always up for learning something new.  I'd love to sync up and see what you put together there!

  • ST_-Superman-_
    Answer ✓

    I created a data set with two fields for this; `route date` (date field) and `Lifts` (integer)

     

    I then created a few beastmodes to engineer a few more fields:

     

    -Route Month: This allows me to see the month name in the flex table card

    MONTHNAME(`route date`)

    -Route Year: This is for the series on my running total graph (I used concat to make sure it ended as a text value and not an integer).  Another note for this field, don't use a name like `Year` for a calculated field.  That name is reserved in some of the back end processes and while it won't break your card, it can cause some strange things to happen from time to time, best to describe it a little more `Route Year`

    concat(YEAR(`route date`))

    -Day of Month: This is for the running total graph as well (x-axis)

    concat(DAYOFMONTH(`route date`))

    Next I had to create a few more beastmodes to use as filters.

    -current month filter:  I use this to allow you to view the current month or previous month.  I find that with most MTD cards, the end users immediately want to see the prior month when the calendar flips over.  With this filter, you can just change it from 'Current Month' to 'last month'

    case when month(`route date`) = MONTH(CURDATE()) then 'current month'
    when MONTH(`route date`) = MONTH(CURDATE())-1 then 'last month'
    when MONTH(CURDATE())=1 and MONTH(`route date`)=12 then 'last month'
    else 'other'
    end

    -YTD filter:  This is used for the flex table because I didn't want to compare 10 days of April this year to a full month last year.  I added a few extra lines to allow for multiple lines.  The data set that I created only went back one year, but this will allow you to show up to 3 years ago (add more if needed)

    case when `route date`<= CURDATE() and YEAR(`route date`) = YEAR(CURDATE()) then 'YTD'
    when `route date`<= DATE_SUB(CURDATE(),interval 1 year) and YEAR(`route date`) = YEAR(CURDATE())-1 then 'YTD'
    when `route date`<= DATE_SUB(CURDATE(),interval 2 year) and YEAR(`route date`) = YEAR(CURDATE())-2 then 'YTD'
    when `route date`<= DATE_SUB(CURDATE(),interval 3 year) and YEAR(`route date`) = YEAR(CURDATE())-3 then 'YTD'
    end

    With all of those fields made (and saved to data set) you are ready to build your cards.

     

    I'll start with the flex table card.  This is going to "replace" your summary number.  This card type has a bit of a learning curve because the bulk of the settings are handled under the general tab.  Also, I filtered the card to only show YTD and Current Month valuesFlex Tables- Two thumbs up!Flex Tables- Two thumbs up!

     

    How to set up the axisHow to set up the axis

     

    General SettingsGeneral Settings

    Also, I prefer to look at most MTD metrics as a running total.  This is because in my business it is not as important to know day over day, but more how things end up at the end of the month.  For example, it does not matter to me if a large sale came in on the 2nd last year but not until the 12th this year, as long as the total for the month is comparable.  You may want to change this visualization based on your needs.4.png

     

     

    Now that our cards are created, make sure they are saved to the same page and lets create a story.

    https://youtu.be/EwCKZ_XfDlE

     


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

    this is amazing work man.  I really appreciate the detail you went through.  I can't mark it as the answer to my questin, but it really is awesome.  I've never used the flex table.  

  • @ST_-Superman-_ ... you're my hero.  I don't even know I need this yet, but your posts make me wish I could geek out with you regularly.  

    DataMaven
    Breaking Down Silos - Building Bridges
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"