Joined: 12 Nov 2007
Location: United States
Online Status: Offline
Posts: 93
Topic: Number range formula Posted: 05 Dec 2007 at 5:35am
I have two different fileds: emp # (datatype: Char) and Salary (datatype: number) Emp no has values 100, 200, 300, 400, .... 1400, 1500 salary has values in Dollar amount.
Based on emp# condition I have to add the salary fileds.
The condition is If emp#>=400 and emp#<1400, sum(Salary).
I used the if then else condition: If emp#>='400' and emp#<'1400' then sum(Salary) else 0
After I used this as a formula, I get zero value. I tried to take out the condition emp#<1400, the formula sums up all the salary (does not care about the condition emp#>=400).
Joined: 19 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 150
Posted: 05 Dec 2007 at 6:15am
It could be to do with the sort order for emp#. You say it is a Char, not a number so it will sort as 100, 1100, 1200,.... 200 etc not 100, 200, 300 ...
Joined: 12 Nov 2007
Location: United States
Online Status: Offline
Posts: 93
Posted: 05 Dec 2007 at 6:38am
Thank you for the reply. that is possible but how would I solve this problem? sb told me that I could use val (emp#) >=400 which I tried but with no change in result. The formula sum(salary) is not using the filter val (emp#) >=400
Joined: 12 Nov 2007
Location: United States
Online Status: Offline
Posts: 93
Posted: 05 Dec 2007 at 8:37am
The Problem was with the sum function. I placed the sum(salary) in the report footer section. What I did different his time was, I used the formula:
If val(emp#) >=400 and val(emp#) <9999 then Salary else 0
What I was using before was If val(emp#) >=400 and val(emp#) <9999 then sum(Salary) else 0
After changing the formula I put the formula field in the details section of the report and calculated the summary of that filed in the report footer section.
That solved my problem. I still do not know why I could not use the If then else condition to sum those values where i use the filter for emp#. It must have sth to do with sum function and If then else condition.
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
IP Logged
