You could create a calculation along the lines of
if date is last year then str(datetrunc('quarter',[Measure]) else str (datetrunc('month',[Measure])
But this is giving me 01/012012......10/01/2011,
I want the word Q4, then Jan-12, Feb-12...and then field is a string field, which I cannot use on a graph(ie..line,bar)
You could use Alex's calculation but changing the if then slightly so it would look similar to the following:
'Q' +str(datename('quarter', [Order Date]))
else left(str(datename('month', [Order Date])),3) + '-' + str(datename('day', [Order Date]))
Thanks Tracy, so I guess theres are no other ways that I can get it onto a chart?
You can create a line even if you have discrete measure. What you want to show is more difficult but would be along the lines of
if date is last year then "Q" + str(datepart('quarter',[Measure]) else str (datepart('month',[Measure])) + str (datepart('year',[Measure]))
To get Jan, Feb as words, you would have to do another if then replacement because there is no text() or format() function you can use
Oh wow thats amazing Alex, Thank you!!
Try this way u will get 75% problem solved.
'Q' + str(DATEPART('quarter',[Date] ) ) + ' ' + str(DATEMONTH('month',[Date] ) )+ ' - ' + right(str(DATEPART('year', [Date] )),2)