Summing Problem

[informer30]

Hi All,

I am trying to plot a graph..but firstly my figures are coming up incorrect. Iam using CRXI Rel2,
I have 2 groups...group 1 'Team' and group 2 'employeenumber'.

Then I have a formula in place as a employeenumber can be part of one of two companies ie.

formula 1 = if companies = 'a' then 1 else 0
formula 2 = if companies = 'b' then 1 else 0

these formula's at detail level.....



The next thing I want to do is to sum the above per group 2 and insert into a graph.

However under gp 2, in the details line I have more than record so when I sum formula 1 or 2, the figures are
incorrect. I have tried various ways around this but no joy so far....anyone any ideas?

If I have missed anything please advise...

Many Thanks

 

[lbass]

Are there more than one company = a per employee that you DO want to count? Or do you expect only a 0 or 1 for each employee?

You could do this. First create a formula {@null} by opening and saving a formula without entering anything. Then create a formula like this:

if {table.company} = 'a' then {table.employeeID} //or companyID?
else tonumber({@null})

If the ID field is not a number, remove the tonumber().

Then insert a distinctcount on the above formula in the chart expert. Repeat for the other condition.

-LB

 

[informer30]

hi lbass,

thanks for the above....maybe I have not explained it good...

an employeenumber can have 2 records under employeenumber group,
ie. employee 675 has 2 records because he belongs to company 'a' and
'b' however his wage will always remain the same. There is another
field which shows the % of time per company and also allows for
one record to appear per employee which is what I am interest. But
how do I make use of this % field to restrict to record per employee
number...


Many Thanks

 

[lbass]

I can't tell what you are trying to do. Please show some sample data and then explain what the problem is by referencing the data. If the goal is to chart, what is chart supposed to do? What is it summarizing?

-LB

 

[informer30]

example data

Group1: ManagerID

Group 2: EmployeeID

Details Line : Employeeid Company Wage %oftime subdept
1234 a 10 75 ac
1234 a 10 25 ac
5412 b 12 35 ab
5412 b 12 65 ab

I want to sum the number of employees per company, per Managerid in the graph. However as above, field %oftime is causing 2 records or sometimes more per employeeid.

I had put in formula's

formula 1 = if companies = 'a' then 1 else 0
formula 2 = if companies = 'b' then 1 else 0

So that I can sum at GF2 and enter into graph, but this is giving me incorrect results.

Please can you advise....

 

[informer30]

Anyone any information on the above....?

Thanks

 

[lbass]

My earlier suggestion should work.

-LB

 

[informer30]

lbass, thanks for the above however I have a 2nd report request which is even more difficult I find.....

example data
Group1: ManagerID
Group2: EmployeeID
Details Line :
Employeeid Company Wage %oftime subdept 1234 a 10 75 ac 1234 b 10 25 ac
20
5412 a 12 35 ab 5412 b 12 65 ab
24

Now I want to plot a pie chart which shows the number of wages per employee id within range 0 to 10, 11 to 20 and 21 to 30 on the change of ManagerID. But I want for example emplpoyee 1234 has summed £20 that to show as one record in the pie chart.


Any one any ideas????

If more information is required..please ask...

Many Thanks