Gets only the values of max date transaction

[#1]

Hi everyone,

I need to realize a straight table to show the sum by customer of the last transaction.

With the example below, I need to show:

for the Customer01 and TypeA the value 15

for the Customer20 and TypeA the value 3

for the Customer01 and TypeB the value 5

for the Customer20 and TypeB the value 9

and so on...

I can find the MaxDate for any Customer/Type, but when I try to use this function to select only my records with set analysis, it doesn't work fine.

maxDate for type:

Max({$<SalesType = {'TypeA'}>}  Aggr(Max(DateId), Customer, SalesType))

The relative Qty:

Sum({$<SalesType = {"TypeA"}, DateId = {"$(=Max({$<SalesType = {'TypeA'}>}  Aggr(Max(DateId), Customer, SalesType)))"} >} Qty)

But it works only for the max value in absolute and not for the max value for Customer

You can find teh Qvw in attach.

Thanks everyone for the support

Cheers.

Andrea

[swuehl]

Set analysis will be evaluated once per chart, not per dimension line, so the max date won't be calculated per customer.

Maybe like this

=FirstSortedValue( {$<SalesType = {"TypeA"} >} Aggr( Sum({$<SalesType = {"TypeA"} >} Qty), Customer, DateId), Aggr( -Only({$<SalesType = {"TypeA"} >} DateId) , Customer, DateId))

[sunny_talwar]

This?

FirstSortedValue({$<SalesType = {"TypeA"}>} Aggr(Sum({$<SalesType = {"TypeA"}>} Qty), Customer, DateId), -Aggr(Only({$<SalesType = {"TypeA"}>} DateId), Customer, DateId))

[maxgro]

sum(aggr(if(max(TOTAL <Customer,SalesType> DateId)=max(DateId), sum(Qty)), Customer, SalesType, DateId))

[#5]

Thans to all for your suggestions.

I think that the maxgro's solution:

sum(aggr(if(max(TOTAL <Customer,SalesType> DateId)=max(DateId), sum(Qty)), Customer, SalesType, DateId))

is better for me because it returns the correct result for all the levels, general total included.

Bye

Andrea