topic Re: Average - Department level Sum in QlikView

Average - Department level Sum

vikas_nandanwar — Sat, 11 Nov 2017 12:28:44 GMT

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

Re: Average - Department level Sum

swuehl — Sat, 11 Nov 2017 12:40:03 GMT

Try as expression

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

with Date being your pivot table dimension.

In a straight table chart, you can use Sum(Total) as expression with total mode on expression tab set to avg of lines.

Re: Average - Department level Sum

vikas_nandanwar — Sat, 11 Nov 2017 12:49:52 GMT

Thanks

This worked

Re: Average - Department level Sum

vikas_nandanwar — Mon, 13 Nov 2017 03:48:24 GMT

Addition to this My data has multiple entries of Dept

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

Pivot

Row Labels	Sum of Total
1-Jan	720
2-Jan	690
3-Jan	840
4-Jan	720
5-Jan	810
Grand Total	3780

Answer = 756

Pleaes help

Re: Average - Department level Sum

shraddha_g — Mon, 13 Nov 2017 04:37:53 GMT

in properties of this expression there will be an option "Show total as". There you can choose 'Avg'

Re: Average - Department level Sum

vikas_nandanwar — Mon, 13 Nov 2017 05:17:25 GMT

Where to see the properties of the expression

Re: Average - Department level Sum

Anil_Babu_Samineni — Mon, 13 Nov 2017 05:22:16 GMT

This option won't work in Pivot table and only available this in straight table.

Why are you looking another option because stefan already gave right direction. Is that working or not?

Re: Average - Department level Sum

vikas_nandanwar — Mon, 13 Nov 2017 05:33:00 GMT

Stefen's expression is working only if there are single entry for dept for single date
If there are multiple entries of dept for a single date the expression doesn't work, like

Date	Dept	Total
1-Jan	A	100
1-Jan	B	90
1-Jan	B	30
1-Jan	C	500

Re: Average - Department level Sum

Anil_Babu_Samineni — Mon, 13 Nov 2017 05:35:04 GMT

True, That means it has aggregate only for dates not for Dept. You can try something like below

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

Re: Average - Department level Sum

vikas_nandanwar — Mon, 13 Nov 2017 05:43:25 GMT

Doesn't work

Re: Average - Department level Sum

Anil_Babu_Samineni — Mon, 13 Nov 2017 05:49:52 GMT

First try to place first and second as inversion like

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

Re: Average - Department level Sum

vikas_nandanwar — Mon, 13 Nov 2017 06:15:15 GMT

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.

Re: Average - Department level Sum

Anil_Babu_Samineni — Mon, 13 Nov 2017 06:55:19 GMT

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))

Re: Average - Department level Sum

vikas_nandanwar — Mon, 13 Nov 2017 09:27:58 GMT

Thanks this worked

Re: Average - Department level Sum

swuehl — Mon, 13 Nov 2017 22:35:10 GMT

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)?

Re: Average - Department level Sum

vikas_nandanwar — Fri, 24 Nov 2017 04:05:37 GMT

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

Re: Average - Department level Sum

vikas_nandanwar — Fri, 24 Nov 2017 05:19:47 GMT

Any suggestions?

Re: Average - Department level Sum

swuehl — Fri, 24 Nov 2017 08:15:32 GMT

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

Re: Average - Department level Sum

vikas_nandanwar — Fri, 24 Nov 2017 09:04:49 GMT

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

Re: Average - Department level Sum

swuehl — Fri, 24 Nov 2017 21:13:25 GMT

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.