sum aggregate function

[#1]

Hi experts

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

        region    city      qty

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

         west      ca        10

         west      ca        10

         west      sa        15

         west      sa        15 

         west      ma       10

         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

[#2]

Dear

       Anyone has experience for this topic ?

Thanks

ZHenfu 

[Chanty4u]

=try this in 

sample:

LOAD * INLINE [

region ,   city,      qty

         west    ,  ca    ,    10

         west   ,   ca   ,     10

         west   ,   sa   ,     15

         west   ,   sa  ,      15

         west    ,  ma  ,     10

         west   ,   ma       10

         ];

strgiht table:

Dim:city

Exp:  sum(Distinct qty)

[Anonymous]

Hi Dragon,

Make you can try the attached qvw.

Hope it helps.

Regards,

Filip

[#5]

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

[#6]

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 

[#7]

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

[Anonymous]

Hi Zhenfu,

Here's the script I used :

Data:

LOAD * Inline

[

region,city,qty

west,ca,10

west,ca,10

west,sa,15

west,sa,15

west,ma,10

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;

[sunny_talwar · Accepted solution]

Can you try this:

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

or

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

or

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

[#10]

Dear Sunny

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

Thanks