Solved: Division of partial sum by other row in other colu... - Qlik Community

MK9885 · ‎2017-05-22

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.

MK9885 · ‎2017-05-23

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.

View solution in original post

sunny_talwar · ‎2017-05-22

Do you have a sample qvw where we can see your setup?

MK9885 · ‎2017-05-22

Sorry, I do not have a sample and can't create a sample due to all key fields in current qvw.

sunny_talwar · ‎2017-05-22

Unfortunately I am not sure how your data looks and what you are after . I hope someone else might understand and help better.

Best,

Sunny

MK9885 · ‎2017-05-22

Np, thanks anyway.

As mentioned

I'm trying to divide a row (Value2, Row1) with partial sum (Value3) and the result is subtracted with another row (Value3 Row1)

sunny_talwar · ‎2017-05-22

Cool ... I am sure someone might be able to help you brother

Anil_Babu_Samineni · ‎2017-05-23

How about this?

Sum(TOTAL <Field1> Value3)/Sum(Value3)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Anil_Babu_Samineni · ‎2017-05-23

Or

Sum(Value2)/(Sum(TOTAL <Field1> Value3)-Sum(Value3))

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

MK9885 · ‎2017-05-23

Hi Anil,

I tried both the expressions, but it doesn't give me any results.

Above image is screenshot of my table.

Here I want to Multiply 22.50 with 366/100-269 to get results in last column

so, (22.50*366/100)-269 should give me -186 but here is it getting calculated as

22.50*269/100-269 to give me -268

Want to divide it by Partial sum of that value not the row.

Thanks.

neelamsaroha157 · ‎2017-05-23

Are you looking for something like this.

Division of partial sum by other row in other column