how to aggregate data using dynamic set of selection

[Anonymous]

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

[kaushiknsolanki]

Hi,

    Here is your solution.

    Have a look at the attached Document

Regards,

Kaushik Solanki

[#3]

Hi Kaushik,

Great Solution. I was trying to achieve via looping in edit script but didn't get success.

Thanks.

Regards,

Sachin A.

[kaushiknsolanki · Accepted solution]

Hi,

    If you got the answer can you mark this as answered.

    So that other user know whats the right ans.

Regards,

Kaushik Solanki

[#5]

I m not the starter of this topic so that option is not visible for me. Let the correct person do the honour

Thanks.

[Anonymous]

Hi Koushik,

Thank you very much for the solution.Will you please explain how is it working internally ?

chandan

[kaushiknsolanki]

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

[Anonymous]

Hi,

You mean the RowNo() = 1 indicates the first row of the output table.

regards,

chandan

[kaushiknsolanki]

Hi,

    Yes you are right.

Regards,

Kaushik Solanki