Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
darren_dixon
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

Please can you help?

Thanks,

Darren

4 Replies

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

Re: Set Analysis - Max Date per customer

Here is a sample attached

Capture.PNG

darren_dixon
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:

  

CustomerAccountTransaction DateFirstSortedValueTransaction Code
16/01/201860
Customer 1Main10/01/201810Cash
Customer 2Main02/01/201840Cash
Customer 3Main16/01/201860Card

When I add the dimension I get extra lines.

CustomerAccountTransaction CodeTransaction DateFirstSortedValue
16/01/201860
Customer 1MainCash10/01/201810
Customer 1MainCard08/01/201830
Customer 2MainCash02/01/201840
Customer 2MainCard01/01/201850
Customer 3MainCard16/01/201860

Re: Set Analysis - Max Date per customer

add it as an expression

Capture.PNG