topic Gets only the values of max date transaction in QlikView

Gets only the values of max date transaction

Sat, 21 Nov 2015 10:37:32 GMT

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

Re: Gets only the values of max date transaction

swuehl — Sat, 21 Nov 2015 11:47:43 GMT

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

Re: Gets only the values of max date transaction

sunny_talwar — Sat, 21 Nov 2015 11:53:18 GMT

This?

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

Re: Gets only the values of max date transaction

maxgro — Sat, 21 Nov 2015 12:02:13 GMT

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

Re: Gets only the values of max date transaction

Mon, 23 Nov 2015 10:21:15 GMT

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