Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
MK9885
Master II
Master II

Division of partial sum by other row in other column

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.

1 Solution

Accepted Solutions
MK9885
Master II
Master II
Author

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

19 Replies
sunny_talwar

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

MK9885
Master II
Master II
Author

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

sunny_talwar

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
Master II
Master II
Author

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

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

Anil_Babu_Samineni

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

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
Master II
Master II
Author

Hi Anil,

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

Capture.PNG

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
Specialist II
Specialist II

Are you looking for something like this.