How to work out which dates are in the last 4 quarters?

[stixmcvix]

Hi there I'm trying to do some report automation, and what I need is to return any record where the date is in the current quarter or the most recent three quarters before that. So for example, with the date today being 29th Jan 2014, we are in Q1 2014, I would therefore need any records returning where the Date is between 01/04/2013 and 29/01/2014 (and hence being Q1 2014, Q4 2013, Q3 2013 or Q2 2013).

I have used the following formula (which works):

=IF((YEAR(TODAY())&" "&"Q"&ROUNDUP((MONTH(TODAY())/3),0))=U2,"yes",IF((YEAR(TODAY()-90)&" "&"Q"&ROUNDUP((MONTH(TODAY()-90)/3),0))=U2,"yes",IF((YEAR(TODAY()-182)&" "&"Q"&ROUNDUP((MONTH(TODAY()-182)/3),0))=U2,"yes",IF((YEAR(TODAY()-273)&" "&"Q"&ROUNDUP((MONTH(TODAY()-273)/3),0))=U2,"yes","no"))))

But then when I pivot the data, the columns are in an odd order:
2013 Q2, 2013 Q4, 2013 Q3, 2014 Q1. I need them in order from left to right oldest quarter to newest quarter.

Any pointers?

Thanks
stixmcvix

 

[Col Delane]

I must admit that my current approach is based on the fact that I don't know much about SQL (other than it's a language used to extract data from relational databases (e.g. ERPs), or in Queries within Access).

I'm interested to know how (briefly) you're using it within a workbook/VBA rather than as part of a query to extract data from an external database/ERP and return same to the workbook - or have I misunderstood your proposal in Posts#3 & #4?

 

[Fazza]

hi, Col

Google helped find old threads that are a bit similar. These are a great source of info. There are many more.

http://www.mrexcel.com/forum/excel-questions/519932-pivot-tables.html

http://www.mrexcel.com/forum/excel-questions/568403-consolidate-pivot-table-sheets.html

http://www.mrexcel.com/forum/excel-questions/747190-pivot-table-sum-value-range.html

http://www.mrexcel.com/forum/excel-questions/657815-pivot-table-hide-pivot-items-unless.html

http://www.mrexcel.com/forum/excel-questions/660141-pivot-table-filtering-include-only-values-greater-than-0-but-not-using-row-column-filters.html

http://www.mrexcel.com/forum/excel-questions/700133-possible-filter-data-into-groups-contain-common-data-point-using-pivot-tables.html

http://www.mrexcel.com/forum/excel-questions/520482-visual-basic-applications-pivot-tables.html

http://www.mrexcel.com/forum/excel-questions/599128-pivot-table-question-formula-subtracts-max-value-min-value-same-column.html

http://www.mrexcel.com/forum/excel-questions/727012-pivot-tables-2007-top-10-plus.html

http://www.mrexcel.com/forum/excel-questions/708810-pivot-table-addding-row-headings-when-calculated-field-entered.html

http://www.mrexcel.com/forum/excel-questions/487821-pivot-table-formula-2007-a.html

http://www.mrexcel.com/forum/excel-questions/613895-pivot-table-filter-out-zeros.html

http://www.mrexcel.com/forum/excel-questions/559148-tough-pivot-table-calculated-field-question.html

http://www.mrexcel.com/forum/excel-questions/661541-how-pull-part-pivot-table-field.html

http://www.mrexcel.com/forum/excel-questions/499681-advanced-run-sum-pivot.html

http://www.mrexcel.com/forum/excel-questions/699617-how-filter-normal-pivot-table-list.html

http://www.mrexcel.com/forum/excel-questions/500309-pivot-table-last-12-months-filter.html

http://www.mrexcel.com/forum/excel-questions/496881-possible-sort-data-day-week-pivot-table.html

http://www.mrexcel.com/forum/excel-questions/680589-any-pivot-table-experts-filter-date-range.html

http://www.mrexcel.com/forum/excel-questions/516684-can-i-do-conditional-sum-pivot-table.html

http://www.mrexcel.com/forum/excel-questions/298048-pivot-tables-ytd-figures.html

And another thread that within it has plenty of links to other information is http://www.mrexcel.com/forum/excel-questions/315768-creating-pivot-table-multiple-sheets.html

For this particular question set up a table with headers "MyDate" and "MyValue" with data immediately under. Dates say 1-Jan-13, 2-Jan-13, 3-Jan-13, etc for a couple of hundred rows and values 1, 2, 3, etc. Give the headers and data a normal defined name (CTRL-F3) of MyTable. Save the file. Maybe best to close it too.

From a new file (CTRL-N), ALT-D-P to start the pivot table wizard, choose external data source, next, get data, Excel files, OK, choose your file, see the table name MyTable, pick some (any) columns and follow the wizard to the end, choosing the option to edit in MS Query.

Within MS Query via the SQL button edit the SQL from whatever you see to become the new SQL. It is just text, so copy & paste from here to replace the original text. When I tested the SQL I posted early in the thread it didn't work - I had to change the "Date" to "Now" and the syntax for the MOD function, and explicitly convert to long data types.

SELECT MyDate, MyValue
FROM MyTable
WHERE CLng(MyDate) > CLng(DATESERIAL(YEAR(Now), MONTH(Now) - 9 - (MONTH(Now)+2) MOD 3, 0))

OK to enter it, if you get a message about not able to graphically represent ... just OK to acknowledge, see the dataset, via the 'open door' icon exit MS Query & complete the pivot table. The resultant worksheet with the pivot table can be moved into the source data file if desired. Now each time the pivot table is refreshed the SQL will filter out data earlier than three quarters from the current quarter.

cheers

 

[Col Delane]

Hi Fazza

Thanks for the explanation and references.

This looks like too much friggin around for my liking (two files vs one, use of MS Query which I know is powerful but I always not so user-friendly, need to know SQL)

What do you see as the advantage/s of this technique?

 

[Fazza]

it is smart, efficient & powerful & can do things you can't otherwise do

You're welcome, Col

Two files is to set it up (when creating a pivot table from a file). Once created, it can become one file.

The mucking around is a one off: and is easier than formulas for anything complex. SQL is easy to learn and even without learning any there is the GUI within MS Query which allows relationships & filtering & summation/averages/minima/maxima/etc similar to MS Access.

Advantages are endless. It is a bit like the difference between using VBA and not using VBA. I provided plenty of links above as examples of advantages. There are threads there that basically could only be solved with this sort of approach: there was no other way.

You can do stuff that you can't otherwise do. You can do things smarter & quicker.

As I wrote previously , there are many, many forum threads where this approach effortlessly - efficiently & quickly - solves what can either not be done by formulas, or if done by formulas takes literally hours for every recalculation of the spreadsheet. Again as previously, it turbo charges pivot tables and even general Excel. Pivot tables from multiple workbooks/data sources is another handy ability.

Also it uses & encourages well structured spreadsheets. Normalised data in tables like a database. Too many forum threads are addressing problems created purely by poorly set up spreadsheets. Like the "I've got 800 worksheets one per employee and now I need to ..." Or 52 worksheets one per week. Or 12 worksheets one per month. You can even have spreadsheets handling huge amounts of data with no formulas. You can modify datasets (maybe in remote databases) and have only a pivot table in the spreadsheet - instead of pulling in 100,000 records across 100 fields and then adding some new fields with formulas before making the pivot table. Saves putting the data in the spreadsheet so saves file size and avoids file bloat/latency.

etc, etc, etc

cheers

 

[Col Delane]

Re: it is smart, efficient & powerful & can do things you can't otherwise do

I'm always game for new techniques, but try to avoid complexity if it doesn't add significant value. Having said that I can see how this one would be useful in certain situations.

I'll read up on it and experiment.

Thanks.

 

[Fazza]

Re: it is smart, efficient & powerful & can do things you can't otherwise do

have fun, Col. regards