Sum distinct Area based on dimension

[#1]

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

[whiteline]

Hi.

May be like this:

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

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

[bgerchikov]

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!

[#4]

Updated post.

[bgerchikov]

Here is expression:

SUM(

Aggr( sum(distinct

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

),AdSize,Client)

)

Please review attached.

Thanks!

[#6]

Hi Ryan,

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

Thanks

AJ

[#7 · Accepted solution]

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