Distance Per Week

[nsandee]

Hello All,

I have to calculate Avg EV Trip Distance for each VIN in the week. This is done by summing all the EVTripDist and dividing by the total of unique VINs for that week.

Here are the fields which are needed

VIN_C

EVTRIPDIST_Q

CHRG_MONDAY

I am attaching sample date for your reference.  We have so many records for VIN, EVTRIPDIST for a particular date ( For Ex: 1/21/2013).

I need to Sum "EVTRIPDIST" and calculate Distinct "VIN_C" for a particular date ( For Ex: 1/21/2013).

Best Regards

Sandeep

Data.txt.zip

[former_member292966 · Accepted solution]

Hi Sandeep,

If you create a group on CHRG_MONDAY.

Do a summary on EVTRIPDIST and drop it into Group Footer 1.

Do another summary on VIN_C and have it do a Distinct Count.  Again, drop it into Group Footer 1.

Now create a formula like:

Sum ({Data_.CRG001_EVTRIPDIST_Q}, {Data_.CHRG_MONDAY}, "daily") / DistinctCount ({Data_.CRG001_VIN_C}, {Data_.CHRG_MONDAY}, "daily");

Again drop this in Group Footer 1.  This will give you the average you are looking for.

Good luck,

Brian

[Former Member]

Hi Sandeep,

To achieve the requirement, you need to follow below steps.

1) Create a group on particular date(CHRG_MONDAY)

2) Create a running total for Sum "EVTRIPDIST" as named as "Sum

       Field Summarize: EVTRIPDIST

       Type of Summary: Sum

       Evaluate: For each record

       Reset: on change of group

3) Create second running total for Distinct "VIN_C" as named as "distinct count"

       Field Summarize: VIN_C

       Type of Summary: Distinct Count

       Evaluate: For each record

       Reset: on change of group

4) Create a formula as name "average" and use the below formula names as running totals.

       "Sum/distinct count"

5) Place the formula of "Average" in  group footer.

Hope the above information will help you.

--Naga.