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

Quick Links

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.

other categories

Product Ideas
Have a Domo product enhancement idea? Submit or upvote on ideas in the Ideas Exchange.
Ideas Exchange
Suggest & vote on new features you would like to see implemented in the Domo Product.
Data Connections
Ask questions about Connectors, Workbench, Cloud Amplifier and get best practices from Domo peers
Connectors
Connectors, Custom Connectors, Writeback
Workbench
Ask questions about Workbench, a secure, client-side solution for uploading your on-premise data to Domo.
Cloud Integrations
Ask questions about Cloud Integrations and Federated Data connection to your data warehouse or lake.
Data & ETL
Ask questions about Magic ETL, SQL DataFlows, DataFusion, Dataset Views and get best practices from Domo peers
Magic ETL
Ask Magic ETL questions and get answers from Domo peers
SQL DataFlows
Ask SQL DataFlow questions and get answers from Domo peers
Datasets
Ask DataFusion and Dataset Views questions and get answers from Domo peers
Visualize & Apps
Ask questions about Beast Mode, Cards, Charting, Dashboards, Stories, Variables and get best practices from Domo peers
Dashboards
Ask Cards, Dashboards, and Stories questions and get answers from Domo peers
App Studio
Ask questions about building apps in App Studio.
Pro-code Components
Ask questions about pro-code components and Domo Bricks and get answers from Domo peers.
Charting & Analyzer
Ask questions about charting and Analyzer and get answers from Domo peers.
Calculations & Variables (Beast Mode)
Ask questions about using calculated fields and Variables (Beast Modes) in Analyzer.
AI & Data science
Ask questions about DomoAI and get answers from Domo peers.
Domo AI & AI Chat
Ask questions about AI Chat and AI assistants.
Managing AI
Ask questions about managing AI with AI Playground, AI projects, AI models, and more.
Jupyter Workspaces
Ask questions about Jupyter Workspaces, Notebooks, and file share.
Automate
Ask questions about App Framework, Workflows, Domo Bricks, Domo Developer, API and get best practices from Domo peers
Workflows
Ask questions about Task Center, building automations with Domo Workflows, and executing JavaScript or Python code with Code Engine.
Alerts
Ask questions about managing alerts in Domo and get answers from Domo peers.
Distribute
Ask questions about Domo Everywhere, Scheduled Reports, Mobile and get best practices from Domo peers
Domo Everywhere
Ask questions about embedded analytics with Domo Everywhere.
Reporting
Ask questions about Scheduled Reports, Report Builder, and Slideshow Publications.
Manage
Ask questions about Governance Administration, Approvals, Teams, Alerts, and Buzz and get best practices from Domo peers
Governance & Security
Ask questions about People, Groups, Roles, Sandbox, Activity log, Buzz, Teams, Approvals and PDP and get best practices from Domo peers
Navigation & Productivity
Ask questions about navigation, Projects & Tasks, Goals, and Buzz chat.
APIs
Ask APIs and Developer.domo.com questions and get answers from Domo peers
Add-ins & Plugins
Ask questions about plugins, Microsoft add-ins, and other third-party software integrations.
Domo Community Gallery
Watch how our Customers are using Domo to solve their complex problems.
Product Releases
Domo support and product teams are here to live-answer questions about the most recent product releases. Please post questions in this Forum board for all users to benefit (rather than submitting a support ticket).
Domo University
Questions or discussions related to Domo University, trainings and certifications
Community Forums
Getting Started
Welcome to Domo's Community Forums! You'll find everything you need to get started in this category.
Community Announcements
Get the latest from Domo's Community Team.
Social Groups
Archive
Old or outdated content that could still be found helpful.