Average Inventory Calculation

[vikas_nandanwar]

Hi,

Avg( Aggr( Sum(Qtyl), Date))

The above expression worked to some extent.

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

I have my data as -

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

Note this is sample data and will contain data from 1st Jan to 31st Oct.

Thanks,

Vikas

[Anil_Babu_Samineni]

What you are drilling and to where? What is the mean of Max date?

[vikas_nandanwar]

My Drill Down is

Dept-WorkCenter-Sub Dept

My Selection is a single date say 2nd Jan
I should get answer as 180 as my qty total

[shraddha_g]

In Which Chart you are trying this?

[Anil_Babu_Samineni · Accepted solution]

Try this?

Avg(Aggr(Sum({<Dept = {'Mfg'}>} Qty),Date))

[vikas_nandanwar]

My aim is

When I drill down to final single date, it should give me sum

All other columns should give me average

[vikas_nandanwar]

This doesn't give me answer, Since my dept has multiple options Mfg is only one of them, I have JW, RM as other options

[vikas_nandanwar]

Simple Bar chart and KPI

[Anil_Babu_Samineni]

Can you elaborate more clear your requirement, If you select 2nd Jan Navigate to this?

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

From this, Data it will give simple as

Sum(TOTAL Qty)

[prma7799]

Have you tried this?

Avg( Aggr( Sum(Qtyl), Date,Dept,WorkCenter,[Sub Dept]))