Beast Mode to compare Values in 1 Column against 2 time periods

Afternoon,

I have access to a dataset at my organization that pulls in competitor data.

The question I want to answer with my card is:

Who is/are the new competitor(s) that showed up last week (fully completed week) compared to competitors from last 12 weeks (exluding the prior completed week)

I have built out a dash that starts at a high level (period over period line bar chart) that shows # of competitors by week and the WoW variance on the secondary axis. I also have some aggregate level competitor metric charts built out too.

What I want to do next is go a little bit deeper and build out a table that will show who are the NEW competitors last week

These are the primary columns in my dataset that I will be using (bolded would be what I will use for the pivot table - the metrics were for the charts I made):

Column A is Date (by Day)
Column B is Competitor
Column C is Metric 1
Column D is Metric 2

Conceptually, I want to build this out via a pivot table

"Column B aka Competitors" Will be a row
The "Values" Column is where I would like to build out a Beast Mode. I've been trying to build a beast mode that will:

Look at Last Week's Competitors compared to all competitors from the previous 12 weeks (excluding last week).
If last week's competitor is NEW then mark it with a 1 and if they are not new, mark it with a 0
add a filter to only show values with "1" since the pivot table can't return "strings".

It would probably look better if I could bucket new competitors as "NEW" and existing as "Existing" but I couldn't get my pivot to return those values when I did a rudimentary beast mode attempt.

The finished table will only list the competitors who are new/have a "1" flag. for the value, I'm fine with it just saying 1.

I have been tinkering with my beast mode for the better part of the work day without avail so here I am . I think I am bungling these "nested" case statements quite badly...

CASE

WHEN WEEK(`Date`) = WEEK(CURRENT_DATE()) - 1 then `Competitor` <> 'Competitor' WHEN WEEK(`Date`) > WEEK(CURRENT_DATE()) - 14 and WEEK(CURRENT_DATE()) < WEEK(CURRENT_DATE()) - 1

THEN '1'
ELSE '0'

END

I'm just trying to make it look at competitors from last week compared to last "14" weeks since I won't be counting the previous week...

thanks!!

Comments

user32470

Edit - is this feasible via beast mode? For context, it's an uphill battle at my organization to modify the datasets (which I don't have access to)

jaeW_at_Onyx

@user32470 , i'll tell you what i tell everyone. ANYTHING (almost ? is possible in Domo if your data is structured the right way.

For this use case, it'd help us if we had a sample of your data and a mockup of your screenshot.

Given what i understand about your data,

assuming you haven't gone out of your way to restructure your data for this requirement, you probably cannot use a beastmode to build the metric you want.

if you put DATE on the axis of your visualization, then on that axis, you only have access to the data with that date. Therefore, if you wanted to ask "what was data from last week... you can't access it b/c you only have access to data from THAT DATE.

If you want to access data across weeks, you could try to create a window function, https://www.youtube.com/watch?v=cnc6gMKZ9R8&list=PLUy_qbtzH0S4CkHBUvpOVpLNJluk6upOn&index=19&t=11s , but for your use case, i would restructure the data in Magic.

In Magic, for each Report_Date (the date field on your card axis), imagine the set of data that you want available. From your description, you want to include rows from that date, as well as rows from the previous week. So in your final dataset you'll have multiple date columns, a Report_Date and an Activity_Date (when the activity actually occurred). Soemthing like this video, https://youtu.be/CDKNOmKClms?t=202

Hope taht helps. good luck!

user32470

Hi Jae,

Thanks for the reply! I should have kept my original post a little more digestible let me re-phrase

Question I am trying to answer with my card:

Who are the NEW competitors (just the name - no other metrics) within our vertical that haven't advertised in the last 12 weeks.

My "solution":

Pivot Table Card

Default view is previous week in first column
- List of Competitors from previous week in 2nd column
New Column (third) that checks to see if any of last week's competitors have shown up in the previous 12 weeks.
I use a Filter and mark "New" so this table will only show New competitors

In my head, I'm thinking it can be done via beast mode?

Create a "group" of competitors based off last 12 weeks
- incorporate a logic that pulls in the competitors from last week and checks if a competitor from last week matches from the 12 week group then spit out the value "Existing" and if it doesn't match then spit out "New"

The dataset is pretty basic. There are additional columns with more metrics (eg: region, spend) but not applicable for this card. Basically its broken by day/competitor like this:

Date	Competitor
8/1/2020	ABC
8/2/2020	DGX
8/3/2020	GDGF
8/4/2020	VDS
8/5/2020	SDF
8/6/2020	QWE
8/7/2020	ASD
8/8/2020	QWE
8/9/2020	ASD
8/10/2020	QWE
8/11/2020	ABC
8/12/2020	DGX
8/13/2020	GDGF
8/14/2020	VDS
8/15/2020	SDF
8/16/2020	QWE
8/17/2020	ASD
8/18/2020	QWE
8/19/2020	ASD
8/20/2020	QWE
8/21/2020	AAA
8/22/2020	AAA
8/23/2020	JGD
8/24/2020	KEL
8/25/2020	ABC
8/26/2020	YOO
8/27/2020	PPP
8/28/2020	QQQ
8/29/2020	RRR

I'm still trying to wrap my head around restructuring the data in magic as you mentioned but it sounds like a "report" date column would just be the reporting week but activity date would just be the exact date that competitor spent money?

Quick Links

Find more posts tagged with

Table

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.