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!

[uacg0009]

So...do you mean it is a bug?

or do we have any other idea to solve that with another expression?

[preminqlik]

try the below exp ..slightly changed

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

[uacg0009]

Hi Prem,

sorry for late reply and thanks for your answer,

I have tried your answer in Qlikview 11.20 SR5, in real data(big data) for one VIP is correct, for all vip it's wrong like last time.

In my data, there are 50,000 vips, but I think it is not because the number of vip, I think it is because in these vips' information, there are many date duplication. Like on 2014-01-10 maybe there are 100 vips bought the same thing.

What I said above is just my guess, I haven't verify it. What do you think about it?

And do you think we can add some statements or conditions to solve it?

[uacg0009]

Anyone can help me with this problem??

[preminqlik]

hi there,

Sorry for late reply , these days am quit busy..

i have tried using bookmark

steps:

clear all

1) take all the VIP ,ITEM,TIME list boxes and search the followng strings in that bookmark
search B in item and press enter
search =aggr(min(TIME),VIP,ITEM) in TIME and press enter

Now add bookmark and say let bookmark name "ITEM_B_MINTIME"

now clear all and in expression add bookmark into current selection by folowing

sum({$*ITEM_B_MINTIME}QTY)

i have attached an application for you .

Hope this helps you atleast ..

[preminqlik]

hi again , in my last reply you can also bookmark with trying the following string in all list boxes :

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

[uacg0009]

Hi Prem,

Sorry for late reply, I have try the =Aggr(DATE(min({<ITEM={"B"}>}TIME),"YYYY-MM-DD"),VIP,ITEM) expression in my real data, it's also for one vip is correct, but for all the vips the data will be wrong. Is that a performance issue?

[preminqlik]

hi u do one thing , reduce the data upto 1000 records  or  sample excel format of original backend table and scramble the data in doc settings and post here ..yu'll get quick answers.

[mambi]

I know it been awhile but better late than never :

did you tried this in you chart : =FirstSortedValue({<ITEM={"B"}>}QTY, TIME)

[vinieme12 · Accepted solution]

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)

)