Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Gets only the values of max date transaction

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.

Qlik.png

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

4 Replies
swuehl
MVP
MVP

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?

Capture.PNG

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

maxgro
MVP
MVP

1.png

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

Not applicable
Author

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