topic Re: Question aggregate in QlikView

Question aggregate

marcel_olmo — Tue, 29 Oct 2013 07:40:59 GMT

Hi guys, if I have a table like this :

Date	Number
20/01/2012	2
20/02/2012	2
20/03/2012	1
20/04/2012	3
20/05/2012	1
20/06/2012	1

How could I get a table like this with set analysis ?

Date	Number
20/01/2012	0
20/02/2012	0
20/03/2012	0
20/04/2012	0
20/05/2012	0
20/06/2012	10

Regards, Marcel.

Re: Question aggregate

salto — Tue, 29 Oct 2013 07:54:34 GMT

Hi Marcel,

PFA my solution... hope this helps!

Regards.

Re: Question aggregate

marcel_olmo — Tue, 29 Oct 2013 08:00:36 GMT

Thanks Salto! That do the work, but I'd love to do it with set analysis because this is a dummy test, but in my case I'm talking about millions of rows, and in the last row of "number", you have to put the sum of all the related values above.

I think it should be withh aggr, because there's no only a "date" field to consider, it has to consider 5 fields more.

Regards, Marcel.

Re: Re: Question aggregate

Tue, 29 Oct 2013 08:04:58 GMT

Hi Guys,

Just a little change to get total on June not on April

best regards

Chris

Re: Question aggregate

marcel_olmo — Tue, 29 Oct 2013 08:28:45 GMT

This is my real case :

Date	ID	Total Value	Number
11/03/2009	R041060	40,25	1
24/08/2009	R041060	74,07	1
05/08/2010	R041060	289,02	1
20/08/2010	R041060	89,93	1

And my desired result would be :

Date	ID	Total New	Number New
11/03/2009	R041060	0	0
24/08/2009	R041060	0	0
05/08/2010	R041060	0	0
20/08/2010	R041060	493,27	4

Re: Question aggregate

salto — Tue, 29 Oct 2013 09:59:46 GMT

Hi,

I have tried with

=aggr(sum(TotalValue),ID)

and

=aggr(count(total Number),ID)

but the result is not exactly what we are looking for... sorry about that,

Re: Question aggregate

marcel_olmo — Tue, 29 Oct 2013 10:05:00 GMT

Thanks Salto for the try! I know this is not that easy.

My proper example of what I'm looking for would be :

This is my current table :

Date	ID	Total Value	Number
11/03/2009	R041060	40,25	1
24/08/2009	R041060	74,07	1
05/08/2010	R041060	289,02	1
20/08/2010	R041060	89,93	1
12/03/2009	R041065	50,25	1
25/08/2009	R041065	84,07	1
07/08/2010	R041065	389,02	1
22/08/2010	R041065	99,93	1

This is my desired table :

Date	ID	Total Value	Number
11/03/2009	R041060	0	0
24/08/2009	R041060	0	0
05/08/2010	R041060	0	0
20/08/2010	R041060	493,27	4
12/03/2009	R041065	0	0
25/08/2009	R041065	0	0
07/08/2010	R041065	0	0
22/08/2010	R041065	623,27	4

Anybody knows how to make it?

Regards, Marcel.

Re: Question aggregate

bdunphy — Tue, 29 Oct 2013 10:22:30 GMT

see attached document.

calculation as below :

If(

MaxString(ID)

below(Total ID)

Sum(Total<ID>[Total Value]),' ')

Re: Question aggregate

er_mohit — Tue, 29 Oct 2013 10:22:42 GMT

Try this

variable

FromDate= =max(DATE)

ToDate= =min(DATE)

EXpression

aggr( RangeSum(above(sum({<DATE={">=$(ToDate) <=$(FromDate)"}>}Sales),0,RowNo())),DATE)

Re: Question aggregate

marcel_olmo — Tue, 29 Oct 2013 10:37:27 GMT

Thanks for the try Mohit!

The min and max Date has to be dinamically, because it depends on more than one field which is the maximum date per ID.

And in the total row will get the same total result as a regular sum.

Here is what I'm trying to get :

Date	ID	Total Value	Number
11/03/2009	R041060	0	0
24/08/2009	R041060	0	0
05/08/2010	R041060	0	0
20/08/2010	R041060	493,27	4
12/03/2009	R041065	0	0
25/08/2009	R041065	0	0
07/08/2010	R041065	0	0
22/08/2010	R041065	623,27	4

Total		1116,54	8

Re: Re: Question aggregate

Tue, 29 Oct 2013 10:59:23 GMT

Hi Marcel

Does this fit your needs?

best regards

Chris

Re: Question aggregate

salto — Tue, 29 Oct 2013 12:04:47 GMT

No way! Values are in the first date

Re: Question aggregate

marcel_olmo — Tue, 29 Oct 2013 12:32:35 GMT

Thanks Brian! It works well in this table. But as I see, it works depending on the Id on the table. What I mean is that in the table I haven't explicitly the ID, and there are more fields to take in count to get the correct sum.

Imagine there's ID, ID1, ID2, ....IDN.

How this will work with a lot of related fields without needing to add this fields into the table?

Many thanks in advance!

Re: Question aggregate

bdunphy — Tue, 29 Oct 2013 12:40:46 GMT

as an example - I use this calculation to give a stock total for Item Subgroup within Customer.

The table itself has approx 10 dimensions. It does not matter how many.

I just check it the row below is a new customer or a new Item Subgroup and if it is - I display the total for that Customer, Item Subgroup.

Try it with your data. or if you can - post a sample qvw

If(

MaxString(Customer)

<>

below(Total MaxString(Customer))

or

MaxString([Item Subgroup])

<>

below(Total MaxString([Item Subgroup]))

,

Sum(Total<Customer,[Item Subgroup]> [Total Stock Wgt]),' ')

Re: Re: Question aggregate

marcel_olmo — Tue, 29 Oct 2013 16:06:33 GMT

Thanks Brian! You're very helpful.

But it still don't work for me.

In the QV document you will have a table with my "current result", my "desired result" and another one with what I'm trying based on your formula-

This is a sample of what I'm doing. Could you tell me what I'm doing wrong?

Regards, Marcel.

Re: Question aggregate

christian77 — Tue, 29 Oct 2013 17:00:30 GMT

Hi Marcel.

I send you a try attached.

Salutes.