4 Replies Latest reply: Jul 3, 2011 7:53 AM by Christian Schröder

# summary amount for latest date entry on customer dimension

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

Chris

• ###### Re: summary amount for latest date entry on customer dimension

One way of doing this using script is given below:

Temp:

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

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.

• ###### summary amount for latest date entry on customer dimension

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

• ###### Re: summary amount for latest date entry on customer dimension

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

• ###### Re: summary amount for latest date entry on customer dimension

Hello Miguel,

that's it - works perfectly!

Thank you very much!