Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

chandan_chakrab
New Contributor

how to aggregate data using dynamic set of selection

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

1 Solution

Accepted Solutions

Re: how to aggregate data using dynamic set of selection

Hi,

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

    So that other user know whats the right ans.

Regards,

Kaushik Solanki

8 Replies

Re: how to aggregate data using dynamic set of selection

Hi,

    Here is your solution.

    Have a look at the attached Document

Regards,

Kaushik Solanki

Not applicable

how to aggregate data using dynamic set of selection

Hi Kaushik,

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

Thanks.

Regards,

Sachin A.

Re: how to aggregate data using dynamic set of selection

Hi,

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

    So that other user know whats the right ans.

Regards,

Kaushik Solanki

Not applicable

Re: how to aggregate data using dynamic set of selection

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

Thanks.

chandan_chakrab
New Contributor

how to aggregate data using dynamic set of selection

Hi Koushik,

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

chandan

how to aggregate data using dynamic set of selection

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

chandan_chakrab
New Contributor

how to aggregate data using dynamic set of selection

Hi,

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

regards,

chandan

how to aggregate data using dynamic set of selection

Hi,

    Yes you are right.

Regards,

Kaushik Solanki

Community Browser