pivot table: aggregated count dependending of If(date=max(date)) condition

[#1]

Hello,

I have this sort of values:

Country	Region	City	ID	Date	Product	Client
C1	R1	C1	1	05/10/2011	P1	C1
C2	R2	C2	1	02/10/2011	P4	C4
C3	R3	C3	3	05/02/2011	P6	C3
C4	R4	C4	4	10/02/2011	P7	C2

I want to have in a pivot table with dimension Country,Region,City this value:

Aggr(

     Count( distinct

               If( Date=Max(Date) and Product=P2 and ..., ID)

     )

     ,Country,Region,Status,ID

)

this sort of expression only give me "0" or "-".

I guess the condition Date=Max(Date) don't work. I want only count the ID if it has reach is maximum date on one or more data lines which validate also the other "IF" condition, aggregated by the dimensions of the pivot table.

I can't send you my application.

Could you help me?

[tanelry]

Maybe create a variable vMaxDate (=Max(Date) ) and use this in the expression.

[jonathandienst]

Hi

Is there any particular reason you need an aggr expression here? If you use this in a table with the same dimensions as the aggregate, I dont think it will do anything useful.

I would try

     Count({<Date = {'$(=Date(Max(Date)))'}, Product = {'P2'}>} ID)

If you have other conditions, add them in the same form as the Product condition.

(Assumes the field Date is in the default date format for your system)

Hope that helps

Jonathan

[#4]

Hi,

The reason to use the aggregate is the max(date) is for each ID.

For example, if I have the data below.

ID,Date

1,06/12/2000

1,NULL

1,15/02/2001

2,03/04/1997

3,06/10/2011

3,06/07/2012

I will only take this lines:

1,15/02/2001

2,03/04/1997

3,06/07/2012

I want to have in my pivot the count of distinct ID by the Country,Region and Status only when the Date value is the max for the ID in order to take only the most recent status fo each ID (Date by ID, not the Total Date over all ID).

[tanelry]

Then you need to aggregate over max date before counting:

Count( distinct

               If( Date=Aggr(Max(Date),Country,Region,Status,ID) and Product=P2 and ..., ID)

)

Hope this helps.

[#6]

Thanks.

But how the ID at the aggregate condition level is matched to the ID in the count function?

My pivot don't use ID in the dimension, that's why I have done this aggregate by Country,Region,Status,ID.

[#7]

Hello,

I haven't found a solution, so I solved it without use expression.

I have used a flag column in database with a "True" flag when I reach my max date for a group Country,Region,Status.

Regards.