Chart by months and week together 1

[johnhugh]

Hi, I'm using Crystal 2010.
Is it possible to have grouping on the x-axis done by month and week?

On my report, I'm showing the rolling purchase order values for the last 12 months, but I would like to show the current month in weeks.

So if the report was run today the x axis would start with the PO total for October up to June and for July it shows every weeks PO total.
Is that possible at all?

 

[pmax9999]

To be honest, I hate CR charts with a passion and therefore avoid them wherever possible. I did find your challenge interesting though, so decided to take a look at it.

My approach would be to create the following formula:

If      Month({Data.Date}) <> Month(CurrentDate)
Then    DateSerial(Year({Data.Date}), Month({Data.Date}) + 1, 1) - 1
Else    {Data.Date}

What this does is, for every month prior to the current month, amend the date to the last day of its respective month, but leave the dates for the current month unchanged. For the purpose of the chart, group on this new formula by week, which for months prior to the current month will actually be full months.


Hope this helps, or at least gives you a direction to head in with in.

Cheers
Pete.

 

[johnhugh]

Thanks Pete.
Much appreciate your help.
The formula works great.

Wondering what you mean by grouping the chart by week?
I'd like to show the x-axis labels as Jan-13, Feb-13 etc. July I either leave blank (no lable) or I show the week number.
To achieve this I've changed the group axis to 'Show Time Scale' and set the numbers format to date.
With that setting I can't display weeks though, only months.

CR charts are not very flexible. I agree with you there.
What are you using for charting?

 

[pmax9999]

When I said Group by week I was referring to the fact that when you group on a Date field you have the option of Monthly/Weekly/Daily etc. "By Week" is the default though.

In regard to the Axis labels, this is where it gets messy, and it seems I may not have thought my solution through as fully as I should have. I think the only way to do it would be to return Strings rather than actual Dates, for example May-13, Jun-13, Jul-13(W1), Jul-13(W2) etc. But then you would need to do a manual sort order on the groups to get the Months and weeks in the right order. Maybe a simpler solution (probably unpopular with the end user) would be 2 charts, one for the completed months and one for the weeks of the current month. Beyond that I am pretty much out of ideas.

As for how I do charts, I find Excel more powerful but I am not sure that would help in this case.

Cheers
Pete