Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
uacg0009
Partner - Specialist
Partner - Specialist

How to calulate the qty of the first purchase that bought specific item per vip?(set analysis and aggr)

Hi all,

I have data like:

MKU QTY1.png

I want to calculate 3 things:

1.The first time the vip bought Item B.

2.The first time the vip purchased.

3.The QTY of B in the first time that the vip bought Item B.

I have done the 1 and 2, but for the 3, I wrote some expressions which only works when I choose one vip, If I didn't choose any vip, the expression didn't work, and I don't know why. You can see them in attached file.

What I want the result is:

MKU QTY2.png

Actually without column 2 and 3 is ok for me, and you also can create a new report and add some calculation dimension to make it.

Because I used these three expressions below, they all didn't work:

=Aggr(SUM({<TIME={"$(=DATE(min({<ITEM={"B"}>}TIME),'YYYY-MM-DD'))"},ITEM={"B"}>}QTY),VIP)

=SUM({<TIME={"$(=Aggr(DATE(min({<ITEM={"B"}>}TIME),'YYYY-MM-DD'),VIP))"},ITEM={"B"}>}QTY)

=Aggr(SUM({<TIME={"$(=Aggr(DATE(min({<ITEM={"B"}>}TIME),'YYYY-MM-DD'),VIP))"},ITEM={"B"}>}QTY),VIP)

I don't want to modify the script in backend, I just want to make it using dimension or expression and of course you can create a new report to calculate this without 1 and 2.

Please feel free to contact me if you have any questions, and if you have any idea please tell me, thank you so much!

20 Replies
uacg0009
Partner - Specialist
Partner - Specialist
Author

That's cool!, Thank you so much.