topic Aggregation formula in QlikView

Aggregation formula

Tue, 15 Jun 2010 13:22:44 GMT

Hello,

I am using a formula .

=-sum(aggr(max([Status])=12 and Original_transportation='0905',(External_HU)))

What this does for me is it gives me distinct count of External_HU's having status=12 and original_transportation='0905'. I want to slightly tweak this formula to not give me count but the sum of tot.wt of each External_HU. The following works to give me the total weight but fails when I insert the condition original_transportation='0905'.

=count(if(Status=12 ,(External_HU)))

Any help appreciated

Regards,

Kiran

Aggregation formula

sparur — Tue, 15 Jun 2010 13:31:25 GMT

may be I don't understand your problem.

but may be that expression will help you:

count({<status = {12}>} External_HU)

Aggregation formula

Tue, 15 Jun 2010 13:46:37 GMT

Thank you sir for the Reply,

I am trying to incorporate 2 conditions when I do a sum(Tot_Wt)

1> ORiginal_TPP='0905'

2> Status=22

If I use a If condition then I manually have to select status=22 and Original TPP= '0905'

I want to be able to select from list box status =22 and then have a bar chart showing me the total wt stacked for 0905, 0900 and 0910 (i.e. I will have 3 expressions in teh chart for the 3 original TPP's)

Thanks.

Aggregation formula

sparur — Tue, 15 Jun 2010 13:52:18 GMT

ok. you need 3 expressions:

1) sum({<Original TPP= {'0900'}>} Tot_Wt)

2) sum({<Original TPP= {'0905'}>} Tot_Wt)

3) sum({<Original TPP= {'0910'}>} Tot_Wt)

So if you select any status (for example 22) you will see what you need. If no any selections (in status field) calculations will be for ALL statuses.

Aggregation formula

Wed, 16 Jun 2010 06:37:21 GMT

Thank you Sir. You have solved my situation.

Last question about this.

The first 2 expressions are fine. I want the third one to include all values that are not included by the first 2 expressions but I am getting incorrect values using the not equal operator. Am I doing something wrong here.

round(sum({<Original_transportation <> {'0905','0900','0910','0920'}>} Tot_wt_KG),10

)