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

Find more posts tagged with

Accepted answers

ST_Superman

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!

How to set up the axis

General 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.

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

All comments

Canioagain

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

ST_Superman

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)

ST_Superman

Sorry...

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

Canioagain

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))

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))

ST_Superman

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:

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

Canioagain

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

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

ST_Superman

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`)

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.

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

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.

How to set up the axis

General Settings

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

Canioagain

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.

DataMaven

@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.