summary amount for latest date entry on customer dimension

[contrax]

Hello,

i've got a table with 3 fields:

-customer

-date

-amount

I want a pivot table with the amount summary for each customer (as dimension) only for the latest date (of each customer). i've tried it with the expression:

sum({$<Date={'$(=Max({1} Date))'}>} Amount)

The result is: only the records with the absolutely latest date over all records have a summary – all others are zero. In the example only customer 2 has 35000  (01.07.2011) - customer 1 has zero. I should have also 19000 for customer 1 (01.04.2011).

Any Ideas? Thanks in advance for your help!

Chris

[nagaiank]

One way of doing this using script is given below:

Temp:

LOAD Customer,Date,Sum(Amount) as DateAmount Group By Customer,Date;

LOAD *,Date(Date#(Date,'MM.DD.YYYY')) as Date;

LOAD * Inline [

Customer,Date,Amount

1,01.03.2011,8000

1,01.03.2011,10000

1,01.04.2011,9000

1,01.04.2011,10000

2,01.03.2011,12000

2,01.03.2011,20000

2,01.04.2011,15000

2,01.04.2011,20000

2,01.07.2011,15000

2,01.07.2011,20000

];

Data:

LOAD Customer,FirstSortedValue(Date,-Date) as LastDate Resident Temp Group By Customer Order By Customer,Date desc;

Left Join (Data) LOAD Customer,Date as LastDate, DateAmount as Amount Resident Temp;

Hope this helps.

[contrax]

Thank you krishnamoorthy.

This is the workaround i also had in my mind. But i thought there was a possibility to do it without script directly in the chart by an expression or dynamic dimension.

I also tried a dynamic dimension like

=IF(Date=aggr(max(Date), customer),Date)

and supress null values. But it won't work...

[Miguel_Angel_Baeyens · Accepted solution]

Hello Chris,

You will need to use the NODISTINCT keyword in the aggr expression, something like

Sum(If(Aggr(NODISTINCT Max(Date), Customer) = Date, Amount))

Check this post and this other post with a very similar issue.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[contrax]

Hello Miguel,

that's it - works perfectly!

Thank you very much!