Average as subtotal in pivot table

Anonymous — Tue, 26 Feb 2013 15:41:56 GMT

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

Re: Average as subtotal in pivot table

Wed, 21 Aug 2013 10:22:33 GMT

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

Re: Average as subtotal in pivot table

qlikpahadi07 — Wed, 21 Aug 2013 10:49:08 GMT

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

Re: Average as subtotal in pivot table

abeyphilip — Wed, 21 Aug 2013 10:50:39 GMT

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

topic Re: Average as subtotal in pivot table in QlikView

Average as subtotal in pivot table

Re: Average as subtotal in pivot table

Re: Average as subtotal in pivot table

Re: Average as subtotal in pivot table