topic Re: Sum distinct Area based on dimension in QlikView

Sum distinct Area based on dimension

Fri, 14 Mar 2014 14:19:55 GMT

Hi,

I have a field, AdSize, in the format ##x##, e.g., 10x2. I am trying to sum the area of the ad sizes using only values with distinct dimensions. Right now, my attempts sum distinct areas, e.g., 10x3 and 15x2 are treated as the same value; I want to treat them as distinct values. For example, in my attached qvw I have Client A with Ad Sizes

10x2

10x3

15x2

12x2

I want to sum (10x2 + 10x3 + 15x2 + 12x2) because they all have distinct dimensions. Right now my sum only adds (20 + 30 +24), because 10x3 and 15x2 both equal 30.

I have tried using

dual(AdSize,SubField(AdSize,'x',1)*Subfield(AdSize,'x',2)) as AdSize1

in the load script and summing distinct AdSize1, but that does not give my desired functionality either.

Any help?

I've attached a qvw outlining my problem.

Thanks and best regards,

Ryan

Re: Sum distinct Area based on dimension

whiteline — Fri, 14 Mar 2014 14:39:15 GMT

Hi.

May be like this:

SubField(AdSize,'x',1) as AdSize1

Subfield(AdSize,'x',2) as AdSize2

Re: Sum distinct Area based on dimension

bgerchikov — Fri, 14 Mar 2014 14:49:41 GMT

Hi Ryan,

Could you please add more clarity in your problem? You have Client A having 4 Sites 10x2, 10x3, 12x2,and 10x3. Do you want to see sum of 10x2=20, 12x2=24,and 10x3=30 ? Can you show desire result for your example.

Thanks!

Re: Sum distinct Area based on dimension

Fri, 14 Mar 2014 15:00:52 GMT

Updated post.

Re: Re: Sum distinct Area based on dimension

bgerchikov — Fri, 14 Mar 2014 15:17:25 GMT

Here is expression:

SUM(

Aggr( sum(distinct

SubField(AdSize,'x',1)*Subfield(AdSize,'x',2)

),AdSize,Client)

)

Please review attached.

Thanks!

Re: Sum distinct Area based on dimension

Fri, 14 Mar 2014 15:24:35 GMT

Hi Ryan,

You can maybe try removing the distinct qualifier from your second and third expressions and see if it helps.

Thanks

Re: Sum distinct Area based on dimension

Fri, 14 Mar 2014 15:54:46 GMT

After some helpful comments, I was able to solve my problem. I've attached a qvw with my desired functionality.