Before & After Training on scale of -6wks to 6wks

Options
user065519
user065519 Member
in Magic ETL

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:

  1. Have the 'before' line -6 through 0 be one color
  2. Have the 'after' line 1 through 6 a different color
  3. 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

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    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"
  • user065519
    user065519 Member

    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

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    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"

Categories