Skip to Content
0

Previous Period Chart

Mar 02, 2017 at 10:16 PM

119

avatar image
Former Member

I've created a summary report that totals sales per month by sales person. The bottom two charts in my example shows John Public's results (in assets sold and in revenue generated) between 01/01/2016 and 12/31/2016. The top two charts are just copies of the bottom charts to help make clear what I'm trying to do which is have the two top charts contain data from the previous period. In this case, 01/01/2015 through 12/31/2015.

The report is grouped first by sales person and then by transaction date (from which the month is derived). The bottom two charts are accurate and, if I wasn't trying to include the previous period's data, the report would be done.

I've tried generating the top two charts in a sub-report but when I do, the top charts for all sales persons are generated for each set of bottom charts.

I'm not sure how to accomplish the result I'm trying to achieve; one page for each sales person with the current period and the previous period displayed on the same page. Any guidance you can provide is appreciated.

overlapreport.png (75.6 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

10 Answers

Ian Waterman Mar 03, 2017 at 08:58 AM
0

You need to set report selection to bring back two years of data.

Then group report on date, when you group by date an additional setting appears in the Insert group dialog box

The Section will be printed

Select for each year.

Place your charts in the Year group header.

Ian

Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Mar 03, 2017 at 11:50 AM
0

Hi Tom,

Here's what you need to do:

1. Make sure the report returns data for both years.

2. Create a formula called @LYAssets with this code:

If Year({Transaction Date}) = 2015 then {Assets Sold}

3. Create a similar formula to Derive Month: @LYMonth:

If Year({Transaction Date}) = 2015 then {Transaction Date}

4. Insert a Line Chart and use the '@LYMonth' formula as the 'On Change of Fields'. Highlight this Date field under 'On Change of' > Order > Change the default from 'for each day' to 'for each month'.

5. Use the '@LYAssets' as the 'Show Values' and click OK.

6. Highlight one of the X-axis labels > Group X-axis Options > Scales > Check 'Don't draw out of Scale Values'.

7. Highlight one of the Y-axis labels > Data Y-axis Options > Scales > Uncheck 'Minimum Value' and set it to 0.1

This is your Assets Chart for 2015.

Repeat Steps 2 through 7 and create separate formulas for 2015 Revenue Chart. Make sure you use the 'Revenue' field in the first formula.

Similarly, repeat steps 2 through 7 for 2016 Charts this time changing the year in the formulas to 2016.

I hope this helps.

-Abhilash

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Mar 17, 2017 at 09:39 PM
0

Great answers! Thank you both! I grabbed the two years of data and (basically) followed your instruction and the report works perfectly! Thank you both again!

The only problem I have now is, if you enter a {?StartDate} of anything other than January, the Y axis will not start there. It always starts with "Jan." I'm sure it's because of how I setup the axis, but I don't know how to make it do what I need it to do.

I set the axis up by grabbing month({InvoiceCreatedDate}) and then specifying a Chart Sort Order in the Chart Expert (1=Jan, 2=Feb, 3-Mar, etc). Like I said, the problem is it always starts with "Jan," even if you run the report with a {?StartDate} in June. For example...

If I run the report with {?StartDate}=06/01/2015 and {?EndtDate}=05/31/2016, the chart begins at January, 2016 then, when you get to June, the 2015 data starts and when you get to the far right of the Y axis (Dec), you then have to go back to the far left of the Y axis to get the Jan through May 2016 data. I hope that makes sense.

What I need (using the example given) is for the chart to start in June of 2015 at the far left of the Y axis and proceed from there. I understand that I'll need to include year({InvoiceCreatedDate}) in order to accomplish this, I just can't find any information on HOW to do it. If you have any ideas I'd be grateful to hear them.

Thank again to both of you. This is a fantastic resource.

Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Mar 20, 2017 at 09:48 AM
0

Hi Tom,

Instead of using a formula to extract the month and then specifying a 'specified sort', try this:

1. Insert a crosstab.

2. Use the actual date field as the 'Row' of the crosstab. Highlight this date field > Group Options > Options tab > check 'Customize Group Name field' > Click 'Use a Formula as Group Name' and click the formula button beside it. Use this code:

totext({table.Datefield}, 'MMM yy')

3. Use the measure formula as the 'Summary Field'.

4. Right-click the crosstab > Insert Chart.

5. Change its type to 'Line'.

-Abhilash

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thank you, Abhilash! I can't tell you how great it is to have experts who are willing to help the less advanced. Your solution works perfectly and I will be applying it to four separate reports.

Thank you again,

Tom Preston

0
avatar image
Former Member Mar 21, 2017 at 04:34 PM
0

Once again, fantastic answer and it works great...thank you!

Now, what appears to be the last hurdle to overcome is limiting the previous year charts (the top charts above) Y axis to previous year date range and the current year charts (the bottom charts above) Y axis to current year date range. By setting the Crosstab Rows to {InvoiceDate}, both the previous year charts and the current year charts are both displaying the entire date ranges of the two data sets on the Y axis...as seen below.

The date range selected for this run was 02/01/2016 through 01/31/2017. That means the previous year Y axis should only display 02/01/2015 through 03/31/2016. Even though the "data" being displayed (the lines) is correct (02/01/2015 through 03/31/2016), the the Y axis is displaying both the previous and the current data set range...02/01/2015 through 01/31/2017.

How can I get the current year Crosstab, and therefore the current year chart, to display only the current year dates and the previous year Crosstab, and therefore the previous year chart, to display only the previous year dates?

For the sake of clarity, below is the Record Selection Formula used to select the two sets of data.

({InvoiceDate}>={?StartDate} and {InvoiceDate}<={?EndDate}

or {InvoiceDate}>=({?StartDate}-365) and {InvoiceDate}<=({?EndDate}-365))

Is there any way to have the current year chart's Y axis display only the current year dates and the previous year chart's Y axis display only the previous year's dates?

Thank you again for your help with this report.

Tom Preston

Share
10 |10000 characters needed characters left characters exceeded
Ian Waterman Mar 21, 2017 at 04:47 PM
0

Moved on since I commented.

Did you group report by Year? If yes place Cross tab and Charts in Year group header and dates should resolve correctly.

Ian

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Mar 21, 2017 at 08:11 PM
0

That did it! I had to play around with grouping a bit because it needs to group by Sales Rep also...but it's doing what I need it to do now. In fact, I'm only using one Cross-tab and one Chart to display both data sets now.

I learned a lot on this report. Thank you both again!

Tom Preston

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Mar 31, 2017 at 09:38 PM
0

First; a sincere thank you again to all who have helped me with this report. It has developed into an amazing tool that will be of great value to our sales manager and our company. That said, I'm still stuck on a chart/date problem.

As seen below, the "current" date range is 03/01/2016 through 02/28/2017. That make the previous period date range 03/01/2015 through 02/29/2016 (I do leap year calculations). The problem occurs when a salesman has a full twelve months of data for the current period but less than twelve months of data in the previous period.

Instead of showing zero values for 03/2015, 04,2015, 05/2015 and 06/2015; the previous period chart STARTS at 07/2015...the month the salesman began working for our company. This makes it very difficult for the sales manager to compare the top and bottom charts because they have different starting points.

How can I force the chart to display the missing months and a zero value for each month for which no invoice exists?


missingdates.png (82.6 kB)
Share
10 |10000 characters needed characters left characters exceeded
Ian Waterman Apr 03, 2017 at 08:34 AM
0

You will need a calendar table which has all months and Years.

This will be your first table and you will then need to left join all other data off this table.

Use the Year and month from this table to group data and you should then see the empty months.

Ian

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Apr 25, 2017 at 06:51 PM
0

Done! Thank you again to everyone who helped. The report is a BIG success and our VP of Business Development couldn't be happier.

Thank you all again - Tom

Share
10 |10000 characters needed characters left characters exceeded