Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
BI Consultant
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.
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...
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.
BI Consultant
Hello Miguel,
that's it - works perfectly!
Thank you very much!