Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
uacg0009
Partner
Partner

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!

1 Solution

Accepted Solutions
vinieme12
Champion II
Champion II

See attached <<EDITED>>

1st:

min(TIME)

2nd

MIN(  {<ITEM={'B'}>} TIME)

3rd:

sum(

AGGR(

if(TIME = min(TOTAL<VIP> {<ITEM={'B'}>}TIME) and ITEM='B' , QTY)

,VIP,TIME,QTY)

)

Capture2.PNG

View solution in original post

20 Replies
jagan
MVP
MVP

Hi,

Try this expression

=Aggr(min({<ITEM={"B"}>}QTY),VIP)

Regards,

jagan.

uacg0009
Partner
Partner
Author

Hi Jagan,

thank you for your answer, for this example, your answer is correct, but actually I have a lot of data, so If I change the VIP 002 data to:

MKU QTY3.png

and use your expression will get wrong result, because I want to calculate the QTY in the first purchase B per VIP.

So do you have any idea to solve that? or if you have any problems please tell me, thank you!

jagan
MVP
MVP

Hi,

Try like this

Data:

LOAD *

INLINE [

    VIP, ITEM, QTY, TIME, ORDER

    001, A, 1, 2014-01-01, 1

    001, C, 2, 2014-01-10, 2

    001, D, 3, 2014-02-10, 3

    001, B, 2, 2014-04-21, 4

    002, B, 3, 2014-02-13, 1

    002, B, 1, 2014-03-23, 2

    003, A, 2, 2014-01-22, 1

    003, C, 1, 2014-03-01, 2

    003, D, 4, 2014-03-10, 3

    004, B, 3, 2014-02-21, 1

    004, A, 1, 2014-02-21, 1

];

LEFT JOIN(Data)

LOAD

VIP,

ITEM,

Min(TIME) AS TIME,

1 AS BMinFlag

RESIDENT Data

WHERE ITEM = 'B'

GROUP BY VIP, ITEM;

Expression : =Min({<BMinFlag={1}>}QTY)

Hope this helps you.

Regards,

jagan.

uacg0009
Partner
Partner
Author

Hi Jagan,

Acutally like I said I don't want to modify the script in the backend, I just want to make it using dimension or expression.

So do you have any idea to solve that?

uacg0009
Partner
Partner
Author

Anyone can help me with that?

preminqlik
Specialist II
Specialist II

hi there,

use the below exp

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

i  am attached application c . ..happy qliking

@premhas

jagan
MVP
MVP

Hi,

Using Aggr() will have performance issues, as far as I know calculating in script is the best choice, otherwise we have to do many calculations in front end, if the data is huge you can see the performance issues.

Regards,

jagan.

uacg0009
Partner
Partner
Author

Hi Prem,

thank you so much! Your answer works in this example in my computer.

But there is a problem when I use the expression in real data, the result is strange, the calculation seems wrong. And I don't know why, because when I choose one vip, it is correct, but for all, it is a wrong result.

In my computer, the qlikview is 11.20 SR5, In my company computer(real data), the qlikview is 11.20 SR1.

So I don't know it is because of the version of qlikview or the big data reason.

Do you have any idea? Your version is ?

preminqlik
Specialist II
Specialist II

same thing hapend to me long time before ...ok post some more sample data wrt. to your realtime data...or reduce it and post it...will see for any alternate