Division of partial sum by other row in other column

[MK9885]

Hello,

I've a requirement where I need to create a new column bu dividing a row from colum by partial sum from another column.

Ex:

Field1 Value1 Value2      Value3        Value4

1          123   444               5

                    564               6

                    453               7

                                       111

2               345                 980

3               456                 888

4               567                 555

Value2 has partial sums as given above.

I've to multiply Value2 row1 (444/111 (partial sum for Value3)-Value3 Row1 which is 5

So,

444/111-5 should give me a new Column Value4

I tries TOTAL and Aggr as well, doesn't seem to work.

stalwar1‌, neelamsaroha1575‌, omarbensalem‌

Thanks.

[Anil_Babu_Samineni]

With out seeing data it is difficult to reply, Have a look this

When should the Aggr() function NOT be used?

[Digvijay_Singh]

Please share sample data in excel and expected output, you will get response faster

[MK9885]

Hi Neelam,
I updated your qvw sample as per my requirement.

You'll find a text box explaining the concern.

Thanks.

[MK9885]

I attached a sample created by Neelam in my above reply.

Note: I'm not using sum for any of my expressions but only counts with complex set expressions and one of my dimension is in %

[neelamsaroha157]

The result in this data would be zero.

It would be better if you can add more meaningful data to it.

[MK9885]

Hi neelamsaroha1575‌

I updated few expressions and tables to replicate mine with new text box notes.

thanks

[neelamsaroha157]

Is this what you are looking for...

[MK9885]

As per the sample data you got correct value cus Value2 field is numeric, if you change it to Alphabets the result will be 0.

Same is happening with me,

I cannot use Aggr(NODISTINCT sum(Value2), Field1)

If you see below image

I'm trying to multiply (22.50*366/100)-269 to get -186.65

I've 2 dimensions which which cannot be Aggregated or Sum I suppose.

If I normally use expression names then it is doing

22.5*269/100-269 is -208 which is wrong.

It's taking first row to calculate instead of partial sum

New Image after I modified the dimension for Value 2

where you see last column is giving same values as Count of Value2 expression

[MK9885 · Accepted solution]

Hi Neelam neelamsaroha1575,

I finally got the solution.. kind of messy cus I'll have an extra column but i'll color code that column to white or gray.

I used set analysis to get partial sum repeated

ex:

if(Field1='1',

Count({<Field1={'1'}>}DISTINCT TOTAL UniqueKEY),

if(Field1='2',

Count({<Field1={'2'}>}DISTINCT TOTAL UniqueKey),

if(Field1='3',

Count({<Field1={'3'}>}DISTINCT TOTAL UniqueKey))))

This gave me partial sum repeating for each row for 1 ,2 and 3

and then

I Named this expression as Total

I used another expression to get my result as

Value2*Total-Count of Value2

but really appreciate your help and also thanks to other folks who helped.

Regards.

[neelamsaroha157]

That is great . Good Luck