Help with report builder calculation

Yahoo Archive
1
Just check the check box on your aggregate to Ignore fields that are zero.


Patrick Winter

-----Original Message-----
From: James Smith [mailto:jsmith@...]
Sent: Friday, February 27, 2004 2:24 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Help with report builder calculation

I have a report that I use to measure on time shipments to customers. The
report I have modified shows individual shipments made for a period to
customers as late or on time and summarizes the information according to
total shipments, shipments on time, shipments late, total dollars shipped,
Dollars shipped on time, dollars shipped late, and average days late
(early).

What I can't figure out is how to calculate "when late, the average days
late". I want to exclude the on time shipments in my calculation. I tried to
perform this calcualtion by creating an aggregate calculation that averaged
the following calculated field IIF(shipdate>reqdate, shipdate-reqdate, 0),
The calculation adds up the total days late, but it divides by the total
number of shipments made instead of just the number of shipments late. I
know why it is doing this. Is there a way to have an IIF statement that is
set up more like IIf (shipdate>reqdate, shipdate - reqdate, otherwise
ignore) ? I hope this makes sense, but this is driving me nuts!

Thanks in advance for your assistance.

Jim



------------------------ Yahoo! Groups Sponsor ---------------------~--> Buy
Ink Cartridges or Refill Kits for your HP, Epson, Canon or Lexmark Printer
at MyInks.com. Free s/h on orders $50 or more to the US & Canada.
http://www.c1tracking.com/l.asp?cid=5511
http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/PhFolB/TM
---------------------------------------------------------------------~->

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
Yahoo! Groups Links
2
I have a report that I use to measure on time shipments to customers. The
report I have modified shows individual shipments made for a period to
customers as late or on time and summarizes the information according to
total shipments, shipments on time, shipments late, total dollars shipped,
Dollars shipped on time, dollars shipped late, and average days late
(early).

What I can't figure out is how to calculate "when late, the average days
late". I want to exclude the on time shipments in my calculation. I tried to
perform this calcualtion by creating an aggregate calculation that averaged
the following calculated field IIF(shipdate>reqdate, shipdate-reqdate, 0),
The calculation adds up the total days late, but it divides by the total
number of shipments made instead of just the number of shipments late. I
know why it is doing this. Is there a way to have an IIF statement that is
set up more like IIf (shipdate>reqdate, shipdate - reqdate, otherwise
ignore) ? I hope this makes sense, but this is driving me nuts!

Thanks in advance for your assistance.

Jim
3
One way to do it would be to make another calculated expression,
IIF(shipdate>reqdate, 1, 0), and total that expression to get the total # of
late shipments and use that to divide by to get the average days late.

I think there's alos a way in the aggregate to ignore certain values, but
can't remember that clearly right now...

-----Original Message-----
From: James Smith [mailto:jsmith@...]
Sent: Friday, February 27, 2004 3:24 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Help with report builder calculation

I have a report that I use to measure on time shipments to customers. The
report I have modified shows individual shipments made for a period to
customers as late or on time and summarizes the information according to
total shipments, shipments on time, shipments late, total dollars shipped,
Dollars shipped on time, dollars shipped late, and average days late
(early).

What I can't figure out is how to calculate "when late, the average days
late". I want to exclude the on time shipments in my calculation. I tried to
perform this calcualtion by creating an aggregate calculation that averaged
the following calculated field IIF(shipdate>reqdate, shipdate-reqdate, 0),
The calculation adds up the total days late, but it divides by the total
number of shipments made instead of just the number of shipments late. I
know why it is doing this. Is there a way to have an IIF statement that is
set up more like IIf (shipdate>reqdate, shipdate - reqdate, otherwise
ignore) ? I hope this makes sense, but this is driving me nuts!

Thanks in advance for your assistance.

Jim




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
Yahoo! Groups Links
4
If I understand your question correctly, you would define
the aggregate to be the average of the days late field and
then select the option to ignore zeroes.

-----Original Message-----
From: James Smith [mailto:jsmith@...]
Sent: Friday, February 27, 2004 3:24 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Help with report builder calculation


I have a report that I use to measure on time shipments to customers. The
report I have modified shows individual shipments made for a period to
customers as late or on time and summarizes the information according to
total shipments, shipments on time, shipments late, total dollars shipped,
Dollars shipped on time, dollars shipped late, and average days late
(early).

What I can't figure out is how to calculate "when late, the average days
late". I want to exclude the on time shipments in my calculation. I tried to
perform this calcualtion by creating an aggregate calculation that averaged
the following calculated field IIF(shipdate>reqdate, shipdate-reqdate, 0),
The calculation adds up the total days late, but it divides by the total
number of shipments made instead of just the number of shipments late. I
know why it is doing this. Is there a way to have an IIF statement that is
set up more like IIf (shipdate>reqdate, shipdate - reqdate, otherwise
ignore) ? I hope this makes sense, but this is driving me nuts!

Thanks in advance for your assistance.

Jim




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
Yahoo! Groups Links