Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
timml_hsu
Contributor III
Contributor III

Show each dimension sum amt by last date

Hi, All

We have data as below:

SEQUSERBUY_DATEAMT
W01User 12018/06/03100
W02User 22018/06/03100
W03User 32018/06/03100
W04User 42018/06/03100
W05User 52018/06/03100
W06User 12018/07/0450
W07User 12018/07/0450
W08User 22018/07/0450
W09User 42018/07/0450

 

We want to show each user last buy date & last buy amt.
We want to show each user last buy date & buy amt at last buy date.
as below :

User1, 2018/7/4, 100
User2, 2018/7/4, 50
User3, 2018/6/3, 100
User4, 2018/7/4, 50

User5, 2018/6/3, 100

We have tried.
Dimension: USER
Last Buy Date expression: MaxString(BUY_DATE)
Last Buy Amt expression: SUM({<BUY_DATE = {"$(=MaxString(BUY_DATE))"}>} AMT)

USERLast Buy DateLast Buy Amt
User 12018/7/4100
User 22018/7/450
User 32018/6/30
User 42018/7/450
User 52018/6/30

 

but it only show last date, miss user3 & user5 last amt.
How could we to do, thanks 

 

4 Replies
Anil_Babu_Samineni

Perhaps simply like

Dimension : User

Expression:

Max(BUY_DATE)

SUM(AMT)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
timml_hsu
Contributor III
Contributor III
Author

Sorry,  my mistake.

We want to show each user last buy date & buy amt at last buy date.

Anil_Babu_Samineni

Could you please modify the output result also

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
timml_hsu
Contributor III
Contributor III
Author

Hi, Anil_Babu_Samineni

thanks your reply
We have modified question.

We want display:

User1, 2018/7/4, 100
User2, 2018/7/4, 50
User3, 2018/6/3, 100
User4, 2018/7/4, 50

User5, 2018/6/3, 100

But, use SUM({<BUY_DATE = {"$(=MaxString(BUY_DATE))"}>} AMT) 
qlikview  show as below:

USERLast Buy DateLast Buy Amt
User 12018/7/4100
User 22018/7/450
User 32018/6/30
User 42018/7/450
User 52018/6/30