Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
Do you have a sample qvw where we can see your setup?
Sorry, I do not have a sample and can't create a sample due to all key fields in current qvw.
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
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)
Cool ... I am sure someone might be able to help you brother
How about this?
Sum(TOTAL <Field1> Value3)/Sum(Value3)
Or
Sum(Value2)/(Sum(TOTAL <Field1> Value3)-Sum(Value3))
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.
Are you looking for something like this.