Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Formula assistance needed Post Reply Post New Topic
Author Message
sandy
Groupie
Groupie


Joined: 10 Jan 2007
Location: United States
Online Status: Offline
Posts: 45
Quote sandy Replybullet Topic: Formula assistance needed
    Posted: 20 Aug 2010 at 8:10am

I have been trying unsuccessfuly to write a formula to handle the following
We charge a fee (based on a formula)depending on a number of occurences that occur on different days.  If the occurence takes place on the same date no fee is charged.

The first occurence is always free
occurence 2 to 5 is charged at $75
6 to 10 is charged at $150
11 or more is charged at $175
however if the occurence occurs on the same date there is no fee and that occurence is not counted when applying the  formula below

I created the following formula:
if {Command.TYPENO} = 1 then 0.00 else
if {Command.TYPENO} in [2, 3, 4, 5] then 75.00 else
if {Command.TYPENO} in [6, 7, 8, 9, 10] then 150.00 else
if {Command.TYPENO} > 10 then 175.00
else

0.00

The problem I have is changing the fee for the situation where an occurence is on the same date and therefore that case is treated as it never occurred and does not impact in the calculation to determine the fee.

For ex: 
Case 1  3/3/10   $0
case 2  3/4/10   $75
case 3 3/12/10  $75
case 4 3/30/10  $75
case 5 5/14/10 $75
case 6 5/22/10 $150
case 7 6/2/10 $150
case 8 6/11/10 $150
case 9 6/11/10 $0  (Same date so it is zero)
caae 10 7/13/10 $150   (this case now becomes case 9 as far as determining the fee)
case 11 7/21/10 $150   ---Note this is not $175 because case 9 was tossed so this case is really only case 10

Can anyone provide guidance???

IP IP Logged
sukhveer
Newbie
Newbie


Joined: 18 Aug 2010
Online Status: Offline
Posts: 22
Quote sukhveer Replybullet Posted: 22 Aug 2010 at 9:10am
easy fix would be... to group on your date field and print detail for each day.
then all you have to do is replace with groupnumber in your formula:
 
if groupnumber = 1 then 0.00 else
if groupnumber in [2, 3, 4, 5] then 75.00 else
if groupnumber in [6, 7, 8, 9, 10] then 150.00 else
if groupnumber > 10 then 175.00
else
0.00
 
--let me know if it works for you.
IP IP Logged
sandy
Groupie
Groupie


Joined: 10 Jan 2007
Location: United States
Online Status: Offline
Posts: 45
Quote sandy Replybullet Posted: 25 Aug 2010 at 7:22am
Thanks for the reply.  I haven't tried it yet but just looking at what you have shown I am wondering if it handles the situation described earlier:
The problem I have is changing the fee for the situation where an occurence is on the same date and therefore that case is treated as it never occurred and does not impact in the calculation to determine the fee.
I will have to give it a try.  Thank you for the response.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.032 seconds.