QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
New Contributor III

Set Analysis - Max Date per customer

Hi,

How do I turn the following data into the straight table below.

This set analysis returns a null:

sum({<[Transaction Date]={'\$(=max([Transaction Date]))'}, [Account]={'Main'}>} [Transaction Value])

Data

CustomerAccountTransaction DateTransaction ValueTransaction Code
Customer 1Main10/01/2018£10.00Cash
Customer 1Sub09/01/2018£20.00Card
Customer 1Main08/01/2018£30.00Card
Customer 2Main02/01/2018£40.00Cash
Customer 2Main01/01/2018£50.00Card
Customer 3Main16/01/2018£60.00Card
Customer 3Sub10/01/2018£70.00Cash
Customer 3Main12/01/2018£80.00Card
Customer 3Main08/01/2018£90.00Card

Straight table

CustomerAccountTransaction DateTransaction ValueTransaction Code
Customer 1Main10/01/2018£10.00Cash
Customer 2Main02/01/2018£40.00Cash
Customer 3Main16/01/2018£60.00Card

Thanks,

Darren

4 Replies
MVP

Re: Set Analysis - Max Date per customer

Try this

FirstSortedValue({<[Account]={'Main'}>} Aggr(Sum({< [Account]={'Main'}>}[Transaction Value]), Customer, [Transaction Date]), -Aggr(Only({< [Account]={'Main'}>} [Transaction Date]), Customer, [Transaction Date]))

MVP

Re: Set Analysis - Max Date per customer

Here is a sample attached

Highlighted
New Contributor III

Re: Set Analysis - Max Date per customer

Hi Sunny,

How would I add the Transaction Code so the table displays like this:

 Customer Account Transaction Date FirstSortedValue Transaction Code 16/01/2018 60 Customer 1 Main 10/01/2018 10 Cash Customer 2 Main 02/01/2018 40 Cash Customer 3 Main 16/01/2018 60 Card

When I add the dimension I get extra lines.

 Customer Account Transaction Code Transaction Date FirstSortedValue 16/01/2018 60 Customer 1 Main Cash 10/01/2018 10 Customer 1 Main Card 08/01/2018 30 Customer 2 Main Cash 02/01/2018 40 Customer 2 Main Card 01/01/2018 50 Customer 3 Main Card 16/01/2018 60
MVP