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

[vikas_nandanwar]

Sum(Total Qty) will give me the answer at the single date i.e. 2-Jan-17, But, when at a whole level say all 300 dates included, my data will sum total all numbers and will give me total of all the dates qty,

[Anil_Babu_Samineni]

But, when at a whole level say all 300 dates included, my data will sum total all numbers and will give me total of all the dates qty,

What does you mean?

[vikas_nandanwar]

I've tried this but this do not give me correct answer

[vikas_nandanwar]

1.     My data has date wise inventory - Date Column

2.     Inventory is available in different department - Dept Column
3.     Department has Sub department and different Work Center - (Sub Dept) & (WorkCenter) Column

4.     Inventory nos are in Qty Column

5.     Co. is another department where it is bifurcated

6.     Type of metal is represented in Type Column (Gold, Silver, Platinum)

Now at a dashboard level  it should look like this with Avg(Aggr(Sum())) function.

But when i Drill down to a single date, say 1st Aug, I should get Total(Sum())

Thanks,

Vikas

[Anil_Babu_Samineni]

Perhaps this?

If(GetCurrentField(GroupName) = 'Date', Sum(TOTAL Qty), Sum(Qty))

[vikas_nandanwar]

How does GetCurrentField(GroupName) expression work?

[Anil_Babu_Samineni]

Will surely explain you, First check in your app further we can discuss more in detail. https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/FieldFunc...

[vikas_nandanwar]

GetCurrentField Function is not active, Instead i am getting GetCurrentSelection

[Anil_Babu_Samineni]

Can you drill to Date and use same expression in Text box then let me know what you are getting, Meantime check vice versa. Don't worry about red wingy line in your expression. Go and continue as it is bug

[vikas_nandanwar]

Its giving me error in expression