topic Average Inventory Calculation in QlikView

Average Inventory Calculation

vikas_nandanwar — Fri, 24 Nov 2017 05:29:18 GMT

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

Re: Average Inventory Calculation

Anil_Babu_Samineni — Fri, 24 Nov 2017 05:42:41 GMT

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

Re: Average Inventory Calculation

vikas_nandanwar — Fri, 24 Nov 2017 05:45:09 GMT

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

Re: Average Inventory Calculation

shraddha_g — Fri, 24 Nov 2017 05:49:24 GMT

In Which Chart you are trying this?

Re: Average Inventory Calculation

Anil_Babu_Samineni — Fri, 24 Nov 2017 05:49:24 GMT

Try this?

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

Re: Average Inventory Calculation

vikas_nandanwar — Fri, 24 Nov 2017 05:49:38 GMT

My aim is

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

All other columns should give me average

Re: Average Inventory Calculation

vikas_nandanwar — Fri, 24 Nov 2017 05:54:14 GMT

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

Re: Average Inventory Calculation

vikas_nandanwar — Fri, 24 Nov 2017 05:54:58 GMT

Simple Bar chart and KPI

Re: Average Inventory Calculation

Anil_Babu_Samineni — Fri, 24 Nov 2017 05:59:33 GMT

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)

Re: Average Inventory Calculation

prma7799 — Fri, 24 Nov 2017 06:00:27 GMT

Have you tried this?

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

Re: Average Inventory Calculation

vikas_nandanwar — Fri, 24 Nov 2017 06:02:56 GMT

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,

Re: Average Inventory Calculation

Anil_Babu_Samineni — Fri, 24 Nov 2017 06:05:17 GMT

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?

Re: Average Inventory Calculation

vikas_nandanwar — Fri, 24 Nov 2017 06:05:54 GMT

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

Re: Average Inventory Calculation

vikas_nandanwar — Fri, 24 Nov 2017 06:15:34 GMT

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

Re: Average Inventory Calculation

Anil_Babu_Samineni — Fri, 24 Nov 2017 06:32:13 GMT

Perhaps this?

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

Re: Average Inventory Calculation

vikas_nandanwar — Fri, 24 Nov 2017 06:39:26 GMT

How does GetCurrentField(GroupName) expression work?

Re: Average Inventory Calculation

Anil_Babu_Samineni — Fri, 24 Nov 2017 06:41:54 GMT

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/FieldFunctions/getcurrentfiel…

Re: Average Inventory Calculation

vikas_nandanwar — Fri, 24 Nov 2017 06:45:08 GMT

GetCurrentField Function is not active, Instead i am getting GetCurrentSelection

Re: Average Inventory Calculation

Anil_Babu_Samineni — Fri, 24 Nov 2017 06:50:57 GMT

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

Re: Average Inventory Calculation

vikas_nandanwar — Fri, 24 Nov 2017 06:58:04 GMT

Its giving me error in expression