Average - Department level Sum

[vikas_nandanwar]

Hi,

My data is as below -

Date	Dept	Total
1-Jan	A	100
1-Jan	B	90
1-Jan	C	500
2-Jan	A	110
2-Jan	B	70
2-Jan	C	480
3-Jan	A	150
3-Jan	B	50
3-Jan	C	580
4-Jan	A	120
4-Jan	B	60
4-Jan	C	510
5-Jan	A	180
5-Jan	B	50
5-Jan	C	550

Pivot -

Row Labels	Sum of Total
1-Jan	690
2-Jan	660
3-Jan	780
4-Jan	690
5-Jan	780
Grand Total	3600

I want Average of Total of Date in Grand Total

Answer = 720

please help

[Anil_Babu_Samineni]

First try to place first and second as inversion like

=Avg( Aggr( Sum(Total), Date, Dept))

Read more Aggr - chart function ‒ QlikView

[vikas_nandanwar]

This doesn't work too
I had to pivot my full data and aggregate the dept, and then use the  =Avg( Aggr( Sum(Total), Date)) expression works with desired results.

[Anil_Babu_Samineni]

So, Does you added Dept field to your field distribution. If so, It should work or else try this with only Date field as dimension

=Avg( Aggr( Sum(TOTAL <Dept> Total), Date))

[vikas_nandanwar]

Thanks this worked

[swuehl]

Not really sure what you mean with saying my expression does not work with multiple records per Date.

This is what I get with your updated sample data and my suggested expression:

Date	Avg( Aggr( Sum(Total), Date))
1-Jan	720
2-Jan	690
3-Jan	840
4-Jan	720
5-Jan	810
Total	756

Isn't 756 what you expect to get?

Maybe you missed the inner aggregation function, Sum(Total)?

[vikas_nandanwar]

The above expression worked to some extent.

Now When i am drilling down to the final date, Its not giving me the correct answer.

My data includes -

Date	Dept	WorkCenter	Sub Dept	Qty	Co. Code	Type
2-Jan-17	Mfg	A	PURE	10	ZZ	Gold
2-Jan-17	RM	B	KARAT	30	XX	Platinum
2-Jan-17	Mfg	C	PURE	30	YY	Silver
2-Jan-17	JW	A	SPRUE	20	ZZ	Platinum
2-Jan-17	Mfg	D	FIL	40	XX	Gold
2-Jan-17	RM	D	BAG	50	YY	Silver
3-Jan-17	Mfg	A	CHK	10	WW	Platinum
3-Jan-17	JW	B	FIL	20	VV	Gold
3-Jan-17	Mfg	C	FILWIP	20	WW	Gold
3-Jan-17	JW	B	CHK	10	VV	Silver
3-Jan-17	RM	C	FIL	30	XX	Platinum
3-Jan-17	RM	B	BAG	30	ZZ	Platinum
3-Jan-17	Mfg	A	CHK	20	YY	Silver
4-Jan-17	JW	B	FIL	50	VV	Gold
4-Jan-17	Mfg	C	FILWIP	10	WW	Gold
4-Jan-17	RM	B	BAG	20	VV	Silver
4-Jan-17	Mfg	C	FIL	40	XX	Platinum
4-Jan-17	JW	A	AAG	40	ZZ	Silver
4-Jan-17	Mfg	D	FIL	20	WW	Gold
4-Jan-17	RM	A	AAG	10	YY	Platinum

I need to check drill down on a single date say 2nd Jan = Total Inventory of Qty = 180

with above expression

[vikas_nandanwar]

Any suggestions?

[swuehl]

What do you get with my expression and what do you expect to see.

[vikas_nandanwar]

Hi,

I am giving you proper answer.
My final aim is-

1.     To get average inventory department wise whenever I am at multiple dates or at clubbed Month Level Avg(Aggr(Sum())) gives me this

2.     When I select a single date from filter, I should get total for that date

Vikas

[swuehl]

What is the expression context? Which dimensions are you using and are you using dimension groups or multiple dimensions? Collapsed dimensions in a pivot table?

Some screenshots may help to see what your exact issue is.