Calculations with Count Field

[MartinaL]

I need to calculate a percentage achieved for bands based on their target number of gigs per month and actual number of gigs.

The Target Gigs is a number field eg 7

The Actual Gigs is a Count of Record ID (shows by artist in layout part)

So I am trying to calculate % achieved by;

(Actual Gigs / Target Gigs) * 100

But is is bringing back really strange numbers.

If I replace actual gigs with a number in the calculation to check if it works it calcalates fine so I assume it is because I am using a count field to try and do the calculation?

[Søren Dyhr]

I'm afriad I might be too daft to make sense out of your question, how is Target Gigs stored, a relation away from the counted Record ID's?? Is it a selfjoin? Are we talking 'bout the aggregate function Count( or is it the summary function turned into counting ID's?

--sd

[MartinaL]

I'm afriad I might be too daft to make sense out of your question, how is Target Gigs stored, a relation away from the counted Record ID's?? Is it a selfjoin? Are we talking 'bout the aggregate function Count( or is it the summary function turned into counting ID's?

Target gigs is a number field in the database Act and it is manually entered.

Actual Gigs is a count of the Record ID on the events database.

[Søren Dyhr]

In other words they're related, and this points in direction of the aggregate function Count( and not the summary function, if the figure is likely to shown in Act's table.

How is this percentage supposed to work, from a layout in the events table or from Act's table?? Or if you're using the summary function from the child table are you bound to sort the found set of records, as well as the GetSummary( function, which uses the breakvalue the foreign key.

But it's still not obvious what you wish to achieve?

--sd

[MartinaL]

Okay hope this explains it;

ACT Table

- field Target Gigs (number field, manual entry)

EVENTS Table

- field Actual Gigs (Calculation, Count(@Record ID))

- field Per Achieved (calculation, Actual Gigs/Target Gigs * 100)

- layout/report shows table with Target Gigs, Actual Gigs and % Achieved, this is shown in a sub part that sorts by Act Name

The % achieved field is the one that isn't calculating correctly

Edited July 17, 2006 by Guest

[Søren Dyhr]

Beyond your weird syntax, should "Actual Gig's" be a summaryfield shouldn't it??

One problem you face here is if GetSummary( is to be employed here is that the breaker field can't be a related ...this is pretty limiting to say the least, and I would instead suggest you move Per Achieved to the other side of the relation, and transform it into an aggregate calc using the relation to establish the number of records related divide with the Target Gig's field in the same table and Round( it to 2.

Over at the reporting side of the matter, do you build an ordinary summary report sorted on the Act_name, in the generated report if you use the wizzard, will you subsummarize by Actname, which puts your related value in a subsummary part.

Here alt-drag the field (duping) and assign the newly created related calc'field to this new field and place it in subsummary part as well. The field needs styling - and this is where your *100 is slightly wrong, a field typed as number can be made to behave according to the formatting you apply to it similar to formatting as currency, is there an option to use %....

--sd

SummaryPer.zip

[MartinaL]

I am a SQL and VB programer by trade so my "weird syntax" is probably because of that, sorry if I am being confusing.

I don't really understand what you mean but I will give it a try anyway.

Thanks :P

[MartinaL]

The weird thing is for the first 4 acts this is what is showing;

Act 1

Target - 7, Actual - 3, % achieved - 14%

Act 2

Target - 7, Actual - 3, % achieved - 14%

Act 3

Target - 4, Actual - 5, % achieved - 25%

I am using exactly the same field calculations as you are but for some reason it is going crazy.

[Søren Dyhr]

Can you upload your file? My guess is that a typecast is playing tricks with you!

--sd

[MartinaL]

Unfortunetly I can't because they are to big, the two files are about 40mb even after being zipped up.

Any clues on how I can try and find out if that is my problem? What should I look for?

[Søren Dyhr]

Check if resulting type of your calc'field is number and is unstored...

--sd

[MartinaL]

Yes unfortunitly it is;

Round(Count( @ Record ID ) / Act::Target Gigs;2)

So that isn't my problem, any other ideas? Sorry I can't put the files up here but they are just way to large :)

[MartinaL]

Ok, record id is text, does this matter if I am only taking a count of it?

[Søren Dyhr]

Round(Count( @ Record ID ) / Act::Target Gigs;2)

It's not the records ID you should use in your calc, but the related tables records. The syntax should have some : in the middle between tablename and fieldname!

--sd

[MartinaL]

So the Record ID from the ACT table?

Round ( Count (Act:@ Record ID ) / Act::Target Gigs ; 2 )

Sorry I am so slow at this, it is probably really frustrating for you

[MartinaL]

Relattionships

Event Table

- @RecordID (Event ID, auto-entered serial)

- Act ID (@RecordID from Act Table)

Act Table

- @RecordID (Act ID, auto-entered serial)

[Søren Dyhr]

Round ( Count (Act:@ Record ID ) / Act::Target Gigs ; 2 )

No... Count(Event::@ RecordID)/Act::Target Gigs

BTW how does @ work in your environmnet, I would never ever use it in the syntax neither would I use spaces in the identifiers??

--sd

[comment]

This is very confusing. Do you mean something like this?

GoalVsPerformance.fp7.zip

[MartinaL]

Yes me either but unfortunetly i am working with DB's that someelse created and they are ingrained throughout the 12 DB system

I have tried this but I am still getting the calculations, which leads me to think that something is being stored or there is a typecast but I can't for the life of me work out where :

I have attached a screen shot of what is being returned and the field set up of the fields being used.

[MartinaL]

Act DB - holds target gigs

[MartinaL]

Okay I have worked out how it is getting 14 and 25!

Once it does the calculation it is dividing by actual gigs again

ie

3/7 = 42% divided by 3 again is 14

5/4 = 125% divided by 5 again is 25

Does this maybe have something to do with by sub summary part?

Any ideas???

Edited July 19, 2006 by Guest

[Søren Dyhr]

If you inspect Comments template, is it doing exactly the same as mine, both the target and the counting is going to be/live in table Acts, you have put it in the wrong table!

However is the reporting going to take place in the events table, and since they're related can the number excatcly as the name of the act be ushered into the summary report on demand.

But by the look of your tables contents ...does it look like a migrated solution. Where the scriplings in the whitepapers on how to get rid of the fm6 legacy, are largely ignored in a sense of "If it ain't broke - don't fix it"

From the principles you can vitness by inspecting your solution, are you excused not to recognize that tunneling have changed considerably, and you would expect the calc'field to be in the table where it's needed ...however today have this sillyness been brought to an end!

--sd

[MartinaL]

I'm not sure what you are saying?

Should I move the report and the % calculation into the Act db?

[Søren Dyhr]

No the calc goes to the same table as the target field. But the reporting goes on in the other table Events.

When you build your summary report do you make the field from a relation away be your breaker field, in this case the Name of the act forget about the dreaded count('ing for a while.

When the summary report behaves , by grouping event's under (they can be erased later) each act's Name ushered in from the related (Parent table) can you via the modifier key alt drag the Name of the act to a new location. What it does is it's duping the field, but here are you prompted to select which field the dupe are going to be. This is where you get this related calcfield with Count( into this layout.

--sd

[MartinaL]

This is working now, and showing the right calculation until I try and filter (or find) the records.

Say I run a find requests to show only those gigs in july 2006 eveything is calculated fine accept that the % achived is still being calculated on the total gigs for the act rather than the total gigs in the new time period ie;

All records

Act - Bender

Target gigs - 7

Actual Gigs - 125

% achived - 1786

Find request for date range 01/07/2006...31/07/2006

Act - Bender

Target gigs - 7

Actual Gigs - 3

% achived - 1786 (should be 43%)

Edited July 19, 2006 by Guest

[Søren Dyhr]

eything is calculated fine accept that the % achived is still being calculated

Ah! I have not seen that the target not is the total....Alright the taget is per month, and you make searches in the Eventstable to get a figure of how well they fullfill the target.

Now this is way into GetSummary( not behaving correctly with breaker values pulled in from a related file. Sorting on the foreignID isn't always as workable as we could wish for - so I felt the urge to lookup the breaker value.

--sd

ppp.zip

[MartinaL]

Nearly there, thanks so much for your help.

Just one last tiny problem. When the actual gigs is less than the target gigs it doens't bring back anything??

[Søren Dyhr]

Are we talking my latest upload here, if you download a fresh copy will a report on febr. 2006 produce 29% and 75% while paused in preview mode.

However is an empty "Target" going to return nothing or rather a ? in the field for the percentage.

I have had a suspicion during this thread that you type everything into the calc's instead of picking and choosing the formulas and the identifiers according to their relational category since you never saw the : in my previous suggestions.

What might be happening is that you gets away with syntax that are allowed but makes little sense.

--sd

Edited July 19, 2006 by Guest

[MartinaL]

Thanks so much for all of your help.

This is working perfectly for all records now accept 2, which leads me to believe there must be something wrong on there ACT records.

Edited July 20, 2006 by Guest