Pivot chart axis ordered by date

[ali32j]

Hi All

I have created a pivot chart based on a query. at present along the bottom axis i have the month, which is ordering aphabetically instead of by month. My query when shown shows ordered by month but when shown on pivot chart it reverts to aphabetic

Can anyone help, i really need this months as they appear throughout the year, below is SQL for my query

SELECT DISTINCTROW Format$([SalesReport].[Month],'mmmm yyyy') AS [Month By Month], Sum(SalesReport.SalesValue) AS [Sum Of SalesValue], Sum(SalesReport.TargetValue) AS [Sum Of TargetValue], Sum(SalesReport.TargetPerc) AS [Sum Of TargetPerc], Sum(SalesReport.GrowthPerc) AS [Sum Of GrowthPerc], [SalesValue]*(100/(100+(100*[GrowthPerc]))) AS [SalesValue-1]
FROM SalesReport
WHERE month>=DateSerial(Year(Date())-1,Month(Date())+1,0)
GROUP BY Format$([SalesReport].[Month],'mmmm yyyy'), [SalesValue]*(100/(100+(100*[GrowthPerc]))), Year([SalesReport].[Month])*12+DatePart('m',[SalesReport].[Month])-1
ORDER BY Year([SalesReport].[Month])*12+DatePart('m',[SalesReport].[Month])-1;



Ali

 

[GingerR]

First, you might run into problems naming a field "Month" since that is a keyword.

That being said, make another calculated field that has month/01/year as a date and sort on it instead:

ReportOrder: Cdate(Month([SalesReport].[Month]) & "/01/"&Year([SalesReport].[Month]))

Sort Ascending on this, and make it invisible.

Then sometimes Access tries to convert my format to their own (i.e. I want "1/07, 2/07," etc but it translates to "Jan 2007, Feb 2007" to try to be helpful) so I force that display month be a string so Access doesn't see it as a potential date and try to change it for me:

DisplayMonth: Cstr(Format$([SalesReport].[Month],'mmmm yyyy'))

and that's the one I have on the chart.

Again, tho, if I were you I'd change the field name to not be "Month".

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[ali32j]

THanks Ginger, took a bit of figuring out but got there in the end using your technique, much appreciated

Ali

 

[ali32j]

Hi All

Could anyone help on code to generate the following

Name Post code Generated code
ADVANCED ELECTRONICS DY41 9HG ADVELEDY4
ADVANCED ENERGY BX11 1NR ADVENEBX1
ADVANCED THINKING PO18 9RB ADVTHIPO1

I would like to take the first 3 letters of each part of the name i.e advanced electronics would be "adv" and "ele", and then take the first 3 letters of post code to automatically generate a code as per the generated code column

I know i can use =LEFT(A2,3&LEFT(B2,3) to get first 3 letters of each cell but what i need is to include the first 3 letters of each part of the cell "Name"

Is this possible? can anyone help?

Thanks

Ali