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

user32470 · October 2020

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

user32470 · October 2020

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 · October 2020

@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 · October 2020

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?

Magic ETL

Magic ETL

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

Welcome!

Comments

Welcome!

Welcome!

Quick Links

Categories