topic how to aggregate data using dynamic set of selection in QlikView

how to aggregate data using dynamic set of selection

Anonymous — Thu, 26 May 2011 05:14:41 GMT

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

Re: how to aggregate data using dynamic set of selection

kaushiknsolanki — Thu, 26 May 2011 06:17:17 GMT

Hi,

Here is your solution.

Have a look at the attached Document

Regards,

Kaushik Solanki

how to aggregate data using dynamic set of selection

Thu, 26 May 2011 06:20:53 GMT

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

kaushiknsolanki — Thu, 26 May 2011 06:24:10 GMT

Hi,

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

So that other user know whats the right ans.

Regards,

Kaushik Solanki

Re: how to aggregate data using dynamic set of selection

Thu, 26 May 2011 06:34:10 GMT

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

Thanks.

how to aggregate data using dynamic set of selection

Anonymous — Thu, 26 May 2011 06:54:26 GMT

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

kaushiknsolanki — Thu, 26 May 2011 07:05:34 GMT

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

how to aggregate data using dynamic set of selection

Anonymous — Thu, 26 May 2011 07:26:26 GMT

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

kaushiknsolanki — Thu, 26 May 2011 07:45:20 GMT

Hi,

Yes you are right.

Regards,

Kaushik Solanki