Question aggregate

[marcel_olmo]

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.

[salto]

Hi Marcel,

PFA my solution... hope this helps!

Regards.

[marcel_olmo]

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.

[#4]

Hi Guys,

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

best regards

Chris

[marcel_olmo]

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

[salto]

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,

[marcel_olmo]

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.

[bdunphy]

see attached document.

calculation as below :

If(

MaxString(ID)

<>

below(Total ID)

,

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

[er_mohit]

Try this

variable

FromDate=    =max(DATE)

ToDate=          =min(DATE)

EXpression

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

[marcel_olmo]

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