9 Replies Latest reply on Nov 6, 2018 2:44 AM by Ciara Brennan

# Average Cost per Event

Hi

How do I calculate the average cost of the an expense - based on the relation between the type of cost,  number of days and of course the cost.

1- the avg costs is calculated where the schema name is anything but 'cancel or 'NA''- and the Travel expense type is ' Taxi' and the based on the Leg Days

- so it would be for 1111 -   (12+12)/12 = 2  and for 222 it would be 34+12+56 /20  = 12.5  and so on

- please do per trip - so trip should be fixed.

2.     count the number of occurrences of each expense try, per trip, and put these on separate columns, ie. one for car and one for taxi,   [i have this in another post - but it did not work - so kindly asking again.

 Trip Number Schema name Leg Days Travel Expense Type Amount in \$ Calculated Field - AVG 1111 Business Trip 12 TAXI 12.00 2.00 1111 Business Trip 12 TAXI 12.00 2.00 1111 Business Trip 10 Car 45.00 2222 Business Trip 20 TAXI 34.00 12.5 2222 Business Trip 20 TAXI 12.00 12.5 2222 Business Trip 20 TAXI 56.00 12.5 2222 Business Trip 20 TAXI 23.00 12.5 3333 Executive 5 TAXI 10.00 8 3333 Executive 5 TAXI 10.00 8 3333 Executive 5 TAXI 20.00 8 4444 Cancel 12 TAXI 30.00 8 4444 NA 12 TAXI 40.00 8 4444 Cancel 12 TAXI 26.00 8

Thank you

Dave.

• ###### 1. Re: Average Cost per Event

Can you explain your Calculation Results for Average. I am Super Confused. What are the logics you are using to calculate 2, 12.5 and 8. If you can Spell it clearly, it can be cracked easily.

Thanks

Deepak

• ###### 2. Re: Average Cost per Event

For 2222 it should be 6.25....(34+12+56+23)/20 as expense type is taxi for all.

Further, as you said  "the avg costs is calculated where the schema name is anything but 'cancel or 'NA'" then your calculation for 4444 is also incorrect but I've not considered this in calculation.

For 2nd Problem, what should be your expected output ?

• ###### 3. Re: Average Cost per Event

Hi

IF [Travel Expense Type]='TAXI' and [Schema name] <> "Cancel" or [Schema name] <> "NA" THEN

{FIXED [Trip Number]: SUM(IF [Travel Expense Type]='TAXI' THEN [Amount in \$] END)/AVG(IF [Travel Expense Type]='TAXI' THEN [Leg Days] END)}

END

what is wrong with  [Schema name] <> "Cancel" or [Schema name] <> "NA"

it does work if I add the or condition, why?

- for the second

To count the number of occurrences of each expense try, per trip, and put these on separate columns.

- I want to to know how many times a particular expense type appears for a trip

For example.

111 -Car will be 1 and Tax will be 2 times.

222 Taxi will be 5 times

To make it nice - there should be a separate calculating field that counts each expense type.

hope that it clear.

• ###### 4. Re: Average Cost per Event

AND/OR statements must be separated by brackets else you'll get incorrect results because now condition is like ([Travel Expense Type]='TAXI' and [Schema name] <> "Cancel") or [Schema name] <> "NA" as conditional statements are evaluated from Left to RIght which is incorrect and Instead of using multiple <> statements put the NOT outside.

IF [Travel Expense Type]='TAXI' and NOT ([Schema name] = "Cancel" or [Schema name] = "NA") THEN

{FIXED [Trip Number]: SUM(IF [Travel Expense Type]='TAXI' and NOT ([Schema name] = "Cancel" or [Schema name] = "NA") THEN [Amount in \$] END)/AVG(IF [Travel Expense Type]='TAXI' and NOT ([Schema name] = "Cancel" or [Schema name] = "NA") THEN [Leg Days] END)}

END

Car or Taxi Count can be calculated with:

{FIXED [Trip Number],[Travel Expense Type]: SUM(IF [Travel Expense Type]='Car' THEN 1 end )}

{FIXED [Trip Number],[Travel Expense Type]: SUM(IF [Travel Expense Type]='TAXI' THEN 1 end )}

1 of 1 people found this helpful
• ###### 5. Re: Average Cost per Event

Amazing!!!!!!

1 of 1 people found this helpful

• ###### 7. Re: Average Cost per Event

I do not see where to mark as correct?

• ###### 8. Re: Average Cost per Event

I can't help in this case because that option is only visible to author. Its near around "This helped me". May be Ciara Brennan can help.

• ###### 9. Re: Average Cost per Event

Hi folks, thread marked Correct, thanks for flagging this

Ciara

1 of 1 people found this helpful