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

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

Fri, 15 Jun 2012 15:50:06 GMT

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?

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

tanelry — Sun, 17 Jun 2012 12:55:24 GMT

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

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

jonathandienst — Sun, 17 Jun 2012 18:03:00 GMT

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

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

Mon, 18 Jun 2012 06:42:34 GMT

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).

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

tanelry — Mon, 18 Jun 2012 07:57:02 GMT

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.

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

Mon, 18 Jun 2012 09:03:49 GMT

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.

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

Tue, 10 Jul 2012 15:28:31 GMT

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.