topic Re: sum aggregate function in QlikView

sum aggregate function

Wed, 25 Nov 2020 16:16:04 GMT

Hi experts

For below data set , i want to get the sum retail qty for region.

region city qty

----------------------------------------

west ca 10

west sa 15

west ma 10

expect result:

region qty

---------------------

west 35 (10+15+10 )

could you please find a solution for this question ?

Thanks in advance!!

Dragon

Re: sum aggregate function

Wed, 18 May 2016 06:06:40 GMT

Dear

Anyone has experience for this topic ?

Thanks

ZHenfu

Re: sum aggregate function

Chanty4u — Wed, 18 May 2016 06:56:07 GMT

=try this in

sample:

LOAD * INLINE [

region , city, qty

west , ca , 10

west , sa , 15

west , ma , 10

west , ma 10

];

strgiht table:

Dim:city

Exp: sum(Distinct qty)

Re: sum aggregate function

Anonymous — Wed, 18 May 2016 07:06:20 GMT

Hi Dragon,

Make you can try the attached qvw.

Hope it helps.

Regards,

Filip

Re: sum aggregate function

Wed, 18 May 2016 07:30:37 GMT

Dear Chanty

Thanks for your qucik response!

for region dim, this formula sum(distinct qty ) will return 25, but expected result is 35 .

Thanks

ZHenfu

Re: sum aggregate function

Wed, 18 May 2016 08:13:27 GMT

Dear

because of the qty of city ca, ma is same, the sum(distinct qty ) for dim region will return 25 ; but as we known that the corrrect result is 35.

this situation confuse me a lot .

it's appreciate your reponse and suggestion

Thanks

ZHenfu

Re: sum aggregate function

Wed, 18 May 2016 08:25:03 GMT

Dear Duchateau

thanks a lot for your response!!

But I'm not able to open your attach file on my test QV ; So could you please help do some explaination about your solution ?

thanks

Re: sum aggregate function

Anonymous — Wed, 18 May 2016 09:02:07 GMT

Hi Zhenfu,

Here's the script I used :

Data:

LOAD * Inline

[

region,city,qty

west,ca,10

west,sa,15

west,ma,10

]

;

Temp:

LOAD

If(Peek(city) = city,0,qty) as total

Resident Data

Order By region,city

;

DROP Table Data;

Total:

LOAD

region,

Sum(total) as qty

Resident Temp

Group By region

;

DROP Table Temp;

Re: sum aggregate function

sunny_talwar — Wed, 18 May 2016 10:12:44 GMT

Can you try this:

Sum(Aggr(Only(qty), region, city))

Sum(Aggr(Avg(qty), region, city))

Sum(Aggr(Sum(DISTINCT qty), region, city))

Re: sum aggregate function

Wed, 18 May 2016 10:43:18 GMT

Dear Sunny

thank you very much for your input. the formula you provided works well .

Thanks