topic Re: Display Avg Value in Total in QlikView

Display Avg Value in Total

Tue, 18 Sep 2012 07:36:26 GMT

Hi Everyone,

I have to show Each Items Avg Value in the pivot table liked shown below.

ItemNo	Quantity	Rate	Tax	ActualValue	Total Cost
I001	10	90	10	100	1000
I001	15	135	15	150	2250
I001	10	90	10	100	1000
I001	15	135	15	150	2250
Total	50	450	50	500	(6500/50) = 130

How to show this?

Thanks

Re: Display Avg Value in Total

Tue, 18 Sep 2012 07:51:36 GMT

In the example I used the expression:

Actual Value: Sum(Rate + Tax)

Avg Value: Sum((Rate+Tax)*Quantity)/Sum(Quantity)

Attached file for ur reference.

Re: Display Avg Value in Total

whiteline — Tue, 18 Sep 2012 09:03:44 GMT

Hi.

You can use dimensionality() function to override a calculation of Total Cost for Total row:

=if(dimensionality()=0, Sum(Sum(aggr(ActualValue*Quantity, ItemNo)))/Sum(Quantity), Sum(ActualValue*Quantity))

Re: Display Avg Value in Total

Tue, 18 Sep 2012 09:06:56 GMT

Hi The Hulk,

What if I have to show all the rows for ItemNo=I001.

Is this expression works ?

e.g. each row contains different EntryNo.

Thanks.

Re: Display Avg Value in Total

Tue, 18 Sep 2012 09:14:29 GMT

Sure, in this case you have to intorduce the new dimension.

Re: Display Avg Value in Total

Tue, 18 Sep 2012 09:29:52 GMT

Hi The Hulk,

Thanks for reply.Even I tried.Its working correctly.

Thanks once again,

Re: Display Avg Value in Total

Tue, 18 Sep 2012 09:33:08 GMT

Hi Whilteline,

Thanks for your reply..I marked your answered as correct because..Use of Dimensionality() is important to show two different calcalation in one column.

Thanks once again.

Re: Display Avg Value in Total

whiteline — Tue, 18 Sep 2012 10:01:10 GMT

I understand

You're welcome.