[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Percent of change in crosstab query

Posted on 2009-04-17
Medium Priority
397 Views
Is there a method in 8.5 to use percent of change between rows within a crosstab?  For example I have a crosstab which has inventory items as rows and months of the year as columns.  The summary fields are sum of quantity purchased 2009 and sum of quantity purchased 2008 for each month.  Would like the 3rd summary to be the % change between Jan 2009 and Jan 2008, etc.   If possible the "dream" would be to have a 4th summary that has the % change between Jan 2009 and Feb 2009

Item                            Jan         Feb
A  2009                        20          5
A  2008                        15          10
% change 09 to 08     33%      -50%
% change Jan09 to
Feb 09                                       -.75%

Tried a method from KHamady but couldn't get that to work correctly.  Has anyone got some ideas?
0
Question by:LBarrett
• 5
• 4

LVL 101

Expert Comment

ID: 24173957
No.  You will have to build a manual crosstab.

mlmcc
0

LVL 2

Expert Comment

ID: 24181593
mlmcc -
sounds like an interesting crosstab, I wouldn't mind being able to do something like that myself...

could you create a series of formulas to do all the math and then crosstab the formulas?
Stacy

0

Author Comment

ID: 24188513
I figured it would not work as crosstab.  How to make a manual when the data looks like shown below.  Parameters are by Div and CustNum and only 2 years will show, current period to date and current -1.  So today it would shown 2009 and 2008 Jan-April.  The divison by zero I have taken care of.  Have built the section related to Quantity Sold by Periods but when I add the Dollars Sold it breaks the quantity for 2009 and 2008 in the middle.  So instead of

Quantity Sold Section
P1     P2  P3
Model4   2009     qty   qty  qty  (with % change between each period as a column)
2008     qty   qty  qty
% change 2009 to 2008 on this row

Dollars Sold Section

Model4   2009     qty   qty  qty  (with % change between each period as a column)
2008     qty   qty  qty
% change 2009 to 2008 on this row

It alternates Qty with dollars by year.

Must be grouping it wrong or possibly have to do Dollars in a subreport?  Long question so I am increasing the points.

``````Div	CustNum	Model	Year	QSoldP1	QSoldP2		QSoldP3	\$Sold1	\$SoldP2	\$SoldP3
40	Acme	model1	2009	3	0		0	25	0	0
40	Acme	model2	2007	0	10		20	0	1344	2688
40	Acme	model2	2009	1	0		0	147.25	0	0
40	Acme	model5 	2008	0	0		0	0	0	0
40	Acme	model3	2008	0	0		0	0	0	0
40	Acme	model3	2007	0	0		0	0	0	0
40	Acme	model6   2007	0	0		0	0	0	0
40	Acme	model3	2008	0	6		0	0	1100.7	0
40	Acme	model4	2009	0	0		4	0	0	315.2
40	Acme	model4 	2008	0	11		6	0	866.8	472.8

How to make the above look like this:
Quantity Sold		P1	P2	% change P1-P2 	Period 3	       % change P2- P3
2009	model1		3	0	% change	         0	       % change
2008			0	0	% change   	0	       % change
% change	% change		       % change
2009 to  2009 to                   2009 to
2008     2008                      2008

Dollars Sold
2009	model1		25	0	% change P1-P2	0	% change P2- P3
2008			0	0	% change P1-P2	0	% change P2- P3
% change	% change		       % change
2009 to  2009 to                   2009 to
2008     2008                      2008
Quantity Sold
2009	model4		0	0	% change P1-P2	4	% change P2- P3
2008	model4		0	11	% change P1-P2	6	% change P2- P3
% change	% change		       % change
2009 to  2009 to                   2009 to
2008     2008                      2008

Dollars Sold
2009	model4		0	0	% change P1-P2	315.2	% change P2- P3
2008	model4		0	866.8	% change P1-P2	472.8	% change P2- P3
% change	% change		       % change
2009 to  2009 to                   2009 to
2008     2008                      2008

``````
0

LVL 101

Accepted Solution

mlmcc earned 1500 total points
ID: 24200652
I'll try to see if I can get it to work

mlmcc
0

Author Comment

ID: 24200830
Thanks I appreciate any help with this one.
0

LVL 101

Expert Comment

ID: 24200908
What value do you want for the %change?

If there isn't data for a model for a given year there will be nothing to compare.

mlmcc
0

LVL 101

Expert Comment

ID: 24200913
Do you want

(P2-P1) / P1

mlmcc
0

Author Comment

ID: 24207865
For the change from 2009 to 2008 what I have now is - previous (to get the 2009 field) minus current (for 2008 field)

whileprintingrecords;
if {quantitysoldP1}<>0 then
totext(((previous({CustSalesHist.QtySoldPeriod1})-{CustSalesHist.QtySoldPeriod1})% {CustSalesHist.QtySoldPeriod1.QtySoldPeriod1})) +'%'
else if {CustSalesHist.QtySoldPeriod1.QtySoldPeriod1} =0 and previous({CustSalesHist.QtySoldPeriod1})=0 then "--"

Could be zero instead of "--" or could be previous({CustSalesHist.QtySoldPeriod1})*100

For the month to month within the same year % change I have

whileprintingrecords;
if {CustSalesHist.QtySoldPeriod3}<>0 then
totext((({CustSalesHist.QtySoldPeriod4}-{CustSalesHist.QtySoldPeriod3})%{CustSalesHist.QtySoldPeriod3}))+'%' else "--"

again it could be zero or Period qty *100

Does that help?
0

Author Comment

ID: 24230233
Haven't heard back and may have found a workaround.  If any suggestions are out there that would be great otherwise I guess I have to close the question.
0

Author Comment

ID: 24237345
Original question was answered regarding the ability to use a regular crosstab and I was able to  come up with a workaround so I am closing the question.  Thanks for the help.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater ā¦
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearlyā¦
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the adminā¦
Please read the paragraph below before following the instructions in the video ā there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, ā¦
###### Suggested Courses
Course of the Month19 days, 3 hours left to enroll