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

[uacg0009]

Hi all,

I have data like:

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:

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!

[jagan]

Hi,

Try this expression

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

Regards,

jagan.

[uacg0009]

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:

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]

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]

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]

Anyone can help me with that?

[preminqlik]

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]

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]

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]

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