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
-
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)
0 -
@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!
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
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?
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive