Crosstab query with qtr and year (1 Viewer)

Razzbarry

Registered User.
Local time
Today, 12:29
Joined
Sep 28, 2018
Messages
28
Hi,
I am working on a forecasting tool and I need to see historical sales by supplier by customer by quarter by year.
I made two crosstab queries one for quarter and one for year but am struggling to get them together with a union. The quarter query doesn't show the year and well the year doesn't show the quarter.
Here is the sql for the year:
TRANSFORM Sum([All Sales to date].[Sales Post Split]) AS [SumOfSales Post Split]
SELECT [All Sales to date].[PL Name], [All Sales to date].[Bill To Name], Sum([All Sales to date].[Sales Post Split]) AS [Total Of Sales Post Split]
FROM [All Sales to date]
GROUP BY [All Sales to date].[PL Name], [All Sales to date].[Bill To Name]
PIVOT Format([Invoice Date],"yyyy");

and the quarter;
TRANSFORM Sum([All Sales to date].[Sales Post Split]) AS [SumOfSales Post Split]
SELECT [All Sales to date].[PL Name], [All Sales to date].[Bill To Name], Sum([All Sales to date].[Sales Post Split]) AS [Total Of Sales Post Split]
FROM [All Sales to date]
GROUP BY [All Sales to date].[PL Name], [All Sales to date].[Bill To Name]
PIVOT "Qtr " & Format([Invoice Date],"q");

Maybe I have the wrong approach altogether... Suggestions are welcome. THe allsales data table has well all the sales for the last 5 years and it is updated daily. I want to be able to access historical sales quickly typically back 5 or 6 quarters.
Thanks,
Razzbarry

p.s. Ultimately I will put this in a form for future use
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:29
Joined
May 7, 2009
Messages
19,169
your last query will do it for you, just edit the PIVOT part:



PIVOT "Qtr " & Format([Invoice Date],"q") & "-" Year([Invoice Date]);

to get it in correct year order, put the Year([Invoice Date]) first in the Pivot part of the query.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:29
Joined
Feb 19, 2013
Messages
16,553
the description of what you require is a bit confusing - can you provide an examples of what your required output looks like

e.g.

customerName, billtoName, Q1, Q2, Q3, Q4, 2018, 2019

Also not sure how your quarterly query is actually working - you mention the need to look at 5 or 6 quarters - your query will combine Q1 for 2018 and 2019 so you will only every have 4 quarters.

edit: arnelgp's sugestion would resolve that
 

Razzbarry

Registered User.
Local time
Today, 12:29
Joined
Sep 28, 2018
Messages
28
Thank you....
I got an error and edited it to: PIVOT "Qtr " & Format([Invoice Date],"q" & "-" & "YYYY");

It works!
 

Razzbarry

Registered User.
Local time
Today, 12:29
Joined
Sep 28, 2018
Messages
28
Hi CJ,
Ultimately I would like it to look like
Supplier Name , customer name Q1 2018, Q2 2018, Q3 2018....Q2 2019. I have other metrics I will add like average runrate and other information like Gross profit.
I am definitely open to more suggestion. Thank you in advance.
Barry
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:29
Joined
May 7, 2009
Messages
19,169
it will be:

PIVOT "Qtr " & Format([Invoice Date],"q\-yyyy");
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:29
Joined
Feb 19, 2013
Messages
16,553
Ultimately I would like it to look like
Supplier Name , customer name Q1 2018, Q2 2018, Q3 2018....Q2 2019
then arnelgp's solution should work for you

I have other metrics I will add like average runrate and other information like Gross profit.
have other crosstabs to determine this information then join them together linking on [PL Name]. Although better to use a PK rather than name.

If this query is to be displayed in a report, you will need to specify column names - see the query properties, but your sql will end up looking something like

Code:
TRANSFORM ...
....
....
PIVOT "Qtr " & Format([Invoice Date],"q" & "-" & "YYYY") [COLOR="Red"]IN ("Qtr 1 - 2018","Qtr 2 - 2018"...)[/COLOR]

And if the report needs to be dynamic - e.g. 'last 6 quarters' will change every quarter, then you will need to be creative with the column name to indicate a specific column and have some code to modify column headers to reflect the actual quarters
 

Users who are viewing this thread

Top Bottom