Average as subtotal in pivot table

[Anonymous]

Hi!

Attached you can see a pivot-table made in QlikView 11. 

The table has 2 dimensions and 3 expressions:

Dimension 1:

=SM9.CASES_MONTHLY_HISTORY.YEAR &'-'& DATE(MONTHSTART(MAKEDATE(SM9.CASES_MONTHLY_HISTORY.YEAR, SM9.CASES_MONTHLY_HISTORY.MONTH,1)), 'MM')

Dimension 2:

%SM9.CASES.REGION

Expression1:

=COUNT({$<SM9.CASES.TYPE={'QUOTE'},SM9.CASES.OCMQM1.LEVERANSE_TILBUD={'LEVERANSEKOORDINERING'},SM9.CASES_MONTHLY_HISTORY.RUB_DELIVERY_STATUS={'Grønn'}>}SM9.CASES_MONTHLY_HISTORY.RUB_DELIVERY_STATUS)
+COUNT({$<SM9.CASES.TYPE={'QUOTE'},SM9.CASES.OCMQM1.LEVERANSE_TILBUD={'LEVERANSEKOORDINERING'},SM9.CASES_MONTHLY_HISTORY.RUB_DELIVERY_STATUS={'Grå'}, %SM9.CASES.OPEN_TIME={'=((NUM(FLOOR(makedate(SM9.CASES_MONTHLY_HISTORY.YEAR,SM9.CASES_MONTHLY_HISTORY.MONTH,1))) - NUM(FLOOR(%SM9.CASES.OPEN_TIME))) < 3)'}>}SM9.CASES_MONTHLY_HISTORY.RUB_DELIVERY_STATUS)

Expression 2:

=COUNT({$<SM9.CASES.TYPE={'QUOTE'},SM9.CASES.OCMQM1.LEVERANSE_TILBUD={'LEVERANSEKOORDINERING'}>}SM9.CASES_MONTHLY_HISTORY.RUB_DELIVERY_STATUS)

Expression 3:

=column (1) / column(2)

At the moment the pivot table only includes data for 1 month, but the number of months will increase up to 12 months.  

My problem is that I want the the total for expression "% Grønne" to show a subtotal as an average for each month.  The average for each month shall be calculated as "%Grønne" for each Regions divided by number of regions .  For the current month: (96,7% + 92,3% + 97,6% + 85,0% + 84,8%) / number of regions = 91,3%

I guess the solution is an expression with use of aggr-funtion, but I am not able to fix this expression on my own.  How can I for each month show an average for the month based on the %Grønne for each Region?

The table does not need to show the result for each region.  Users do only need to see the month and the average for the month based on all Regions.

I hope anyone can help me with a solution. 

Best regards

Torunn

[#2]

Even I am facing the same problem I can User Straight Table and force it to Avg but have no clue in PIVOT

[qlikpahadi07 · Accepted solution]

I have found the Solution for me and here this will help you :

its Simple

Just right down the  "% Grønne"  expression as :

=Aggr(Aggr(column (1) / column(2) ,Region,Month))

I hope this will work  Cheers

[abeyphilip]

You will have to use dimensionality.

See if attached QVW helps.

You may have to change the expression to "dimensionality()=1" as you have one more dimension than in my eg.

Regards,

Abey