Determining if a record falls on the first day of a quarter and if not collect it in the next Quarte

Randyb
Randyb Member
in Magic ETL

I have been presented with an interesting task, I have a dataset that contains all of our sales records and our company measures SSS (Same Store Sales) Year over year.

I solved this fairly easily by inputting the first RO date into the dataset for each location.

Now the accounting department has come back and said they really want to do this measure on a quarterly basis, so if that FirstRODate > Day 1 of the Quarter then don't pick it up until the next quarter.

So something like: 

Case when Quarter(`FirstRODate`)=QUARTER(DAYOFMONTH('1'))then `OpsNetSales` Else 0
End 

I know that Beast Mode doesn't work, I'm struggling with how to identify if the FirstRODate is actually the 1st day of the quarter in which it falls.

If that is to difficult I could also look at something that says if the MonthDay ('FirstRODate')<>1 then Quarter('FirstRODate') +1 Maybe some sort of concat statement that gives it a FirstROQuarter status.

I'm sure this is possible, just not sure of the best approach.

Thanks for your help

Randy

Best Answer

  • Randyb
    Randyb Member
    Answer ✓

    Its not you it was me, I knew what I wanted but not being sure how to get there it was a bit of a hike, but your responses started me down the right path.

    I made a couple more changes to get all the date possibilites covered, here is what it looks like.

     

    case when DATE_FORMAT(`FirstRODate`,'%d') > '01' and year(`FirstRODate`) = year(`FirstRODate`) AND Quarter(`FirstRODate`)<>4 Then Concat((QUARTER(`FirstRODate`)+1),'/',YEAR(`FirstRODate`))
    when DATE_FORMAT(`FirstRODate`,'%d') > '01' and year(`FirstRODate`) = year(`FirstRODate`) AND Quarter(`FirstRODate`) = 4 Then Concat((QUARTER(`FirstRODate`)-3),'/',YEAR(`FirstRODate`)+1)
    when DATE_FORMAT(`FirstRODate`,'%m%d') in('0101','0401','0701','1001') then Concat(QUARTER(`FirstRODate`),'/',YEAR(`FirstRODate`))
    when DATE_FORMAT(`FirstRODate`,'%d') = '01' AND MONTH(`FirstRODate`) Not in('01','04','07','10')and year(`FirstRODate`) = year(`FirstRODate`) AND Quarter(`FirstRODate`)<>4 Then Concat((QUARTER(`FirstRODate`)+1),'/',YEAR(`FirstRODate`))
    Else Concat((QUARTER(`FirstRODate`)+1),'/',YEAR(`FirstRODate`))
    End

Answers

  • Tomo
    Tomo Contributor

    Hi @Randyb

     

    BEAST MODE

    case
      when DATE_FORMAT(`FirstRODate`,'%m%d') in('0101','0401','0701','1001') then `OpsNetSales`
      else 0
      end

    How about this BEAST MODE expression?

  • Randyb
    Randyb Member

    So if Im reading this correctly it is saying that if the firstRODate matches the 1st of any one of those quarters then OPSNetSales.

    If this is correct then how do I move it to the next quarter if it doesn't match.

    So to describe it better if I have a FirstRODAte for a location that is 01/01/2018 then all RO's would be included in a Q1 collection and onwards.

    However if the FirstRODate was 01/02/2018 then the collection of Sales would not start until Q2 onwards.

    I'm not sure this beast mode would do this, although I think you are very close, allot closer than I was.

    Randy

  • rahul93
    rahul93 Contributor

    @Randyb 

     

    Let me know if this works:

    case when DATE_FORMAT(`FirstRODate`,'%m/%d') = '01/01' and year(`FirstRODate`) = year(`FirstRODate`) - 1 and QUARTER(`FirstRODate`) = 4 then 'Q1' 
    when DATE_FORMAT(`FirstRODate`,'%m/%d') = '04/01' and year(`FirstRODate`) = year(`FirstRODate`) and QUARTER(`FirstRODate`) = 1 then 'Q2'
    when DATE_FORMAT(`FirstRODate`,'%m/%d') = '07/01' and year(`FirstRODate`) = year(`FirstRODate`) and QUARTER(`FirstRODate`) = 2 then 'Q3'
    when DATE_FORMAT(`FirstRODate`,'%m/%d') = '10/01' and year(`FirstRODate`) = year(`FirstRODate`) and QUARTER(`FirstRODate`) = 3 then 'Q4' 
    END

     

     

  • Randyb
    Randyb Member

    There is a syntax error in it somewhere, still trying to find out where.

    Randy

  • Randyb
    Randyb Member

    Ok, so I've come up with perhaps a better approach, although this Beast Mode isn't pretty it looks like it would work if someone can help clean it up.

    I decided if I creat a new field which basically converts the FirstRODate to a new date, I'm sure there is a way to do this based off of the other two answers where I don't need to input the year, but I think this will work.

     

    Case when `FirstRODate` >'01/01/2018' AND `FirstRODate` <'03/31/2018' then '04/01/2018'
    End

    I tested this and it did find the locations with a FirstRODate in this quarter that didn't start on Jan 1st and created an entry for 04/01/2018.

    The year is an important piece of information but can't figure out how I could optimize this Beast Mode short of creating an entry for every quarter of every year.

    If thats what I've got to do then I'll get started.

    Thanks for the help so far, I think I'm close.

    Randy

  • rahul93
    rahul93 Contributor

    Hey @Randyb


    I have edited the question, use that as the label field (x axis for the vertical bar chart) and use the value field as your value scale. This beast mode will allocate your values accordingly. 

    You will also need a beast mode for the filtering the data to show the chart for one year (if your dataset is not already setup that way, given below): 

    case when `FirstRODate` <= Current_date() AND `FirstRODate` > date_add(current_date() , interval -1 year) then 'yes' else 'no' end


    Hit yes on the filter and apply. 

    Sorry for the previous code, that was silly of me. Let me know if this works.

  • Randyb
    Randyb Member

    Not sure that will work because they are going to want to see this as Year over year along with a number of other Region, Market etc filters.

    I've gotten even closer yet, this Beast Mode gives me Quarter and Year and now advances the quarter by 1, except in Q4 I end up with Q5.

     

    case when DATE_FORMAT(`FirstRODate`,'%m/%d') >= '01/01' and year(`FirstRODate`) = year(`FirstRODate`)Then Concat((QUARTER(`FirstRODate`)+1),'/',YEAR(`FirstRODate`))
    End

     

    If you can let me know how to get Q5 to become Q1 and advance the year I think that might do it.

    Then all I'd have to do is set the filter to be something like <= 1/2018

    I'm sure I missing a ton and this isn't the correct approach although I like trying to solve problems like this.

  • rahul93
    rahul93 Contributor

    That should do it for all years.I havent worked with YOY so i cannot tell you. But that code should aggregate all data that falls in a quarter regardless of the year.

  • Randyb
    Randyb Member

    Ok, I think I have it, this Beast Mode does exactly what I want, now my question to everyone is am I wanting the right thing, am I going to be able to group the data on this like I can with any other date field??

     

    case when DATE_FORMAT(`FirstRODate`,'%m/%d') >= '01/01' and year(`FirstRODate`) = year(`FirstRODate`) AND Quarter(`FirstRODate`)<>4 Then Concat((QUARTER(`FirstRODate`)+1),'/',YEAR(`FirstRODate`))
    when DATE_FORMAT(`FirstRODate`,'%m/%d') >= '01/01' and year(`FirstRODate`) = year(`FirstRODate`) AND Quarter(`FirstRODate`) = 4 Then Concat((QUARTER(`FirstRODate`)-3),'/',YEAR(`FirstRODate`)+1)
    End

     

    You guys set me on the right path, I hope

    Randy

  • rahul93
    rahul93 Contributor

    @Randyb

     

    Well that query is completely different from what i got from your question. Hope you are on the right path! ?

  • Randyb
    Randyb Member
    Answer ✓

    Its not you it was me, I knew what I wanted but not being sure how to get there it was a bit of a hike, but your responses started me down the right path.

    I made a couple more changes to get all the date possibilites covered, here is what it looks like.

     

    case when DATE_FORMAT(`FirstRODate`,'%d') > '01' and year(`FirstRODate`) = year(`FirstRODate`) AND Quarter(`FirstRODate`)<>4 Then Concat((QUARTER(`FirstRODate`)+1),'/',YEAR(`FirstRODate`))
    when DATE_FORMAT(`FirstRODate`,'%d') > '01' and year(`FirstRODate`) = year(`FirstRODate`) AND Quarter(`FirstRODate`) = 4 Then Concat((QUARTER(`FirstRODate`)-3),'/',YEAR(`FirstRODate`)+1)
    when DATE_FORMAT(`FirstRODate`,'%m%d') in('0101','0401','0701','1001') then Concat(QUARTER(`FirstRODate`),'/',YEAR(`FirstRODate`))
    when DATE_FORMAT(`FirstRODate`,'%d') = '01' AND MONTH(`FirstRODate`) Not in('01','04','07','10')and year(`FirstRODate`) = year(`FirstRODate`) AND Quarter(`FirstRODate`)<>4 Then Concat((QUARTER(`FirstRODate`)+1),'/',YEAR(`FirstRODate`))
    Else Concat((QUARTER(`FirstRODate`)+1),'/',YEAR(`FirstRODate`))
    End

Categories