Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Friends,
I want to calculate month wise opening balance from the following table.
Product Day Qty Tag
1 01/04/2010 20 O
2 01/04/2010 25 O
3 01/04/2010 10 O
1 01/04/2010 51 T
2 01/04/2010 50 T
3 01/04/2010 75 T
3 01/05/2010 40 T
2 01/05/2010 32 T
1 01/05/2010 11 T
1 01/06/2010 15 T
2 01/06/2010 20 T
3 01/06/2010 19 T
O=Opening balance
T=Transaction during the year
OB=Opening balance
From the above transaction table how to make a report in the following format:
Month OB Qty Total
Apr 55 176 231
May 231 83 314
June 314 54 368
plz help me out.
regards
chandan
Hi,
If you got the answer can you mark this as answered.
So that other user know whats the right ans.
Regards,
Kaushik Solanki
Hi,
Here is your solution.
Have a look at the attached Document
Regards,
Kaushik Solanki
Hi Kaushik,
Great Solution. I was trying to achieve via looping in edit script but didn't get success.
Thanks.
Regards,
Sachin A.
Hi,
If you got the answer can you mark this as answered.
So that other user know whats the right ans.
Regards,
Kaushik Solanki
I m not the starter of this topic so that option is not visible for me. Let the correct person do the honour
Thanks.
Hi Koushik,
Thank you very much for the solution.Will you please explain how is it working internally ?
chandan
Hi,
You wanted this output
Month OB Qty Total
Apr 55 176 231
May 231 83 314
June 314 54 368
Now the field Qty can be obtain with the expression as
sum({<Tag = {"T"}>}Qty) which says give me sum where tag is T. So in the month od Apr you have Tag as 'O' also which will be skipped in this expression so you get only 176 which is a sum of qty when tag is T.
Now for OB you need first value as 55 which is the sum of qty when tag is O. and then from second row ownward you need 55 + 176 = 231.
So i used below expression.
if(RowNo() = 1,sum({<Tag = {"O"}>}Qty),Above(Qty)+Above(OB))
Means when the RowNo = 1 then give only the sum of qty where Tag is O else take first Value of Qty and OB and add them.
Hope you understood the way of working
Regards,
Kaushik Solanki
Hi,
You mean the RowNo() = 1 indicates the first row of the output table.
regards,
chandan
Hi,
Yes you are right.
Regards,
Kaushik Solanki