topic Re: aggr in QlikView

aggr

n1ef5ng1 — Sun, 14 Jul 2013 09:33:42 GMT

Hi gurus,

I know the only solution to solve my problem is to use aggr, but I have not use aggr before and have no idea on how to make it works

Type	Number of People	Vacation Stay in Days	*Total Days(Column(2) Column(3))**
Male	4	5	20
Male	4	1	4
Male	5	12	60
Male	3	3	9
Male	7	2	14

The sum of Total Day will be 107 after adding up the total days. This figure of 107 is the desired output for me for a specific column.

Here is my desired table layout

Type	Total Number of People	Total Days
Male	4+4+5+3+7 = 23	107
Female	12	90
Others	7	31

I believe I need to use aggr but anybody can help me out on the expression that I should use?

Thanks very much in advance

Re: aggr

kaushiknsolanki — Sun, 14 Jul 2013 09:39:14 GMT

Hi,

Considering the first table as your data.

Create a chart with dimension as Type

Expressions as

1. Sum(Number_of_people)

2. Sum(Number_of_people * Vacation_Stay_in_Days)

If not kindly provide your data.

Regards,

Kaushik Solanki

Re: aggr

n1ef5ng1 — Sun, 14 Jul 2013 09:48:21 GMT

You are right on the first table with the expreession. With the value on the second expression, I will then use subtotal to get the total.

However, my dersire output is the second table, the figure are derived after i subtotal on the second expression. (see 107 figure in bold)

Re: aggr

kaushiknsolanki — Sun, 14 Jul 2013 09:57:50 GMT

Hi,

Kindly send your sample qvw file for more understanding.

Regards,

Kaushik Solanki

Re: aggr

n1ef5ng1 — Sun, 14 Jul 2013 10:12:30 GMT

for now I can't send a sample file yet.

basically the first table is like a virtual table to total up the value

I will try to upload later

Re: aggr

kaushiknsolanki — Sun, 14 Jul 2013 10:19:19 GMT

Hi,

It will be helpful if you can create the same situation on test data and upload here, that will give us a better view of your problem.

Regards,

Kaushik Solanki

Re: aggr

Anonymous — Sun, 14 Jul 2013 12:32:19 GMT

1 - Create a straight table chart, add 'Type' as dimension.

2 - Add an expression AGGR( SUM(NumberofPeople), Type)

I hope this helps!

Regards

MultiView

Re: aggr

rustyfishbones — Sun, 14 Jul 2013 13:21:52 GMT

Here is a video on using aggr function

http://www.youtube.com/watch?v=Hmjm3vQX9HE&sns=em

Hope it helps

Re: aggr

n1ef5ng1 — Sun, 14 Jul 2013 14:46:48 GMT

unable to provide sample data,

not sure if I have stated the problem clearly,

hopefully someone can help in this

Basically I want the virtual table A to derive the total number of days to be 107

and create a expression(which i believe is AGGR), to state 107 without any additional column or table

Re: aggr

n1ef5ng1 — Sun, 14 Jul 2013 15:24:46 GMT

Attached file as below,

In the qlikview file, there is two table call desire table and Total Box.

I having problem putting a value on Total Box Dwell Days in Desire Table

I am able to achieve the value if I can create a table call Total Box,

The value for Over Type,Month 10, will be (3*2) + (6*4) = 30,

Value fo Important Type, Month 10, will be (4*1) + (8*5) = 44

Thus, the value of 30 and 44 should be in Desire table,

Thanks very much!

I believe is aggr but I have no clue on how to do it

Re: aggr

swuehl — Sun, 14 Jul 2013 15:38:51 GMT

Try

=sum( aggr( Days*sum(Box), Month,Type,Days ))

Re: aggr

n1ef5ng1 — Sun, 14 Jul 2013 15:53:00 GMT

Sorry about the duplicate post.

The expression diden give me the result that I want, did you manage to get the value using the example file?

Re: aggr

swuehl — Sun, 14 Jul 2013 16:05:24 GMT

Yes, I got 30, 44, ...

Check attached.

Re: aggr

n1ef5ng1 — Sun, 14 Jul 2013 16:15:11 GMT

you got it correct. gosh but not me, I will try to find the logic behind these in 9 hours time, got to sleep now. thanks alot for ur help. basically aggr expression, you have to list out the expression follow by the dimension of the virtual table?

Re: aggr

n1ef5ng1 — Tue, 06 Aug 2013 02:32:42 GMT

Hi guys,

Not sure if this is bug or something, I managed to solve this problem thanks to swuehl, he attached a working file for reference.

I am wondering is aggr a realiable code to use? As I copied the table to another file, the value changed with the same database, i did not change any script too.

When i upload the report to the server, the figure of the aggr column changed too.

why is this so? is it a bug?