Before & After Training on scale of -6wks to 6wks
I am trying to graph out a call center metric for students 6wks prior to training and 6wks after they took training in 1 week intervals.
There are hundreds of students that took the training over a long period of time therefore the before and after is not a single date in time.
I do not have two date fields to compare, therefore i'm converting both to weeks.
- WEEKOFYEAR(`Offering End Date`) -- gives me the actual week of the year vs. fiscal / last day of training
- **I think i need to subtract 30 from this to match the fiscal week? - Our fiscal wk 1 is Feb 1st.
- RIGHT(`Dim - CalendarFiscal Week`,2)*1 -- gives me the fiscal week of call center measurement
I've attached a picture of what the graph looks like now using the SPC graph.
I want to do three things now:
- Have the 'before' line -6 through 0 be one color
- Have the 'after' line 1 through 6 a different color
- Create a summary number showing the difference between the two so you can see if it increased after training
Can someone help me figure out how to achieve this?
case when WEEKOFYEAR(`Offering End Date`) = RIGHT(`Dim - CalendarFiscal Week`,2)*1 then '0Wk'
when WEEKOFYEAR(`Offering End Date`) - RIGHT(`Dim - CalendarFiscal Week`,2)*1 = '1' then '-1wk'
when WEEKOFYEAR(`Offering End Date`) - RIGHT(`Dim - CalendarFiscal Week`,2)*1 = '2' then '-2Wk'
when WEEKOFYEAR(`Offering End Date`) - RIGHT(`Dim - CalendarFiscal Week`,2)*1 = '3' then '-3Wk'
when WEEKOFYEAR(`Offering End Date`) - RIGHT(`Dim - CalendarFiscal Week`,2)*1 = '4' then '-4Wk'
when WEEKOFYEAR(`Offering End Date`) - RIGHT(`Dim - CalendarFiscal Week`,2)*1 = '5' then '-5Wk'
when WEEKOFYEAR(`Offering End Date`) - RIGHT(`Dim - CalendarFiscal Week`,2)*1= '6' then '-6Wk'
when WEEKOFYEAR(`Offering End Date`) - RIGHT(`Dim - CalendarFiscal Week`,2)*1 = '-1' then '1wk'
when WEEKOFYEAR(`Offering End Date`) - RIGHT(`Dim - CalendarFiscal Week`,2)*1 = '-2' then '2Wk'
when WEEKOFYEAR(`Offering End Date`) - RIGHT(`Dim - CalendarFiscal Week`,2)*1 = '-3' then '3Wk'
when WEEKOFYEAR(`Offering End Date`) - RIGHT(`Dim - CalendarFiscal Week`,2)*1 = '-4' then '4Wk'
when WEEKOFYEAR(`Offering End Date`) - RIGHT(`Dim - CalendarFiscal Week`,2)*1 = '-5' then '5Wk'
when WEEKOFYEAR(`Offering End Date`) - RIGHT(`Dim - CalendarFiscal Week`,2)*1= '-6' then '6Wk'
else 'null' end
Comments
-
given that you have a graph, it's unclear to me what you do and don't have done.
that said a few things to consider.
1) ignore fiscal year. or fiscal week. as i understand it, your question has nothing to do with a calendar year, it's just "did behavior change before and after training." that's irrespective of time of year / fiscal year.
2) you said you don't have two dates to compare, but for your activity table, you must have "activity date" and "training date." course ... if it's possible for people to take the training twice, you'll have to find an interesting way to model that...
Assuming you have your two dates, then instead of immediately converting your data to weeks, i would create an isKeep flag ( 1 or 0 and use a CASE statement to select is activity date within +/- 6*7 days (using datediff) of "training date")
do a quick row count to make sure the data makes sense.
if that part works then do datediff / 7 round up or down depending on how you want week 1 represented and use that as your X axis. job done!
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 -
Can you explain in more detail?
I do not have two date fields. The training date is a date field.
The "activity date" is formatted as year/week and not days. It is a text field. YYYY-Wxx
Thank you
0 -
convert your week into either the first or last day of the week and then you can easily do a datediff.
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
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive