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

Randyb

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

Randyb

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

Tomo

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

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

@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

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

Randy

Randyb

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

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

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

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

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

@Randyb

 

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

Randyb

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