topic Re: Average Calculation Issue in Pivot Table in QlikView

Average Calculation Issue in Pivot Table

AC — Thu, 12 Apr 2018 14:21:05 GMT

I have an issue in calculating the average, so looking for some help here.

I would like to show avg counts per each day/week/month...etc insisted of total counts in the text boxes and Pivot table.

I am trying to match the avg counts from text box values with the Pivot table values (from the attached file) for that in Pivot table i have tried some expressions(along with dates trying to add Pivot tables dimensions as well) to match Avg Fleet/Rented values with the text box values values, It is matching only when we select a particular day (Select a day from list box and see) but when you deselect the day or when you clear the selections avg text box values are not matching with Pivot table values.

Of-course it should match when we select Corp_BP_Type filter as well.

Second one is need to set up Document On Open trigger for Week and Quarter fields as like other date fields.

Regards,

Anil

Re: Average Calculation Issue in Pivot Table

AC — Fri, 13 Apr 2018 03:30:20 GMT

Guys, Any help on my issue??

Re: Average Calculation Issue in Pivot Table

stabben23 — Fri, 13 Apr 2018 06:33:10 GMT

Here is a New qvw

Re: Average Calculation Issue in Pivot Table

AC — Fri, 13 Apr 2018 13:26:17 GMT

Thank you for the response Staffan. Its matching for Monthly,Week and Day but when clear the selections and when you select Quarter and Year its not matching. See below

Re: Average Calculation Issue in Pivot Table

stabben23 — Fri, 13 Apr 2018 13:32:28 GMT

Change to Date here instead of Day

=sum(aggr(avg(aggr(if(only(Vehicle_Use) = 'LEASE',
count({<Vehicle_Use-={''},Unit_type-={''},Current_Cust_Number ={'699999','699998'},Vehicle_FlagStatus_Group2 = {'AVAILABLE','RENTED','DEADLINED'},Veh_Cap_Status-={''}>}FLEET_COUNT),
count({<Vehicle_Use-={''},Unit_type-={''},Vehicle_FlagStatus_Group2 = {'AVAILABLE','RENTED','DEADLINED'},Veh_Cap_Status-={''}>}FLEET_COUNT)),Date,Corp_BP_Type,Unit_type)),Corp_BP_Type,Unit_type))

Re: Average Calculation Issue in Pivot Table

AC — Fri, 13 Apr 2018 20:08:59 GMT

Thank you, now its working fine.