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

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

uacg0009 — Wed, 16 Jul 2014 05:25:59 GMT

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!

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

jagan — Wed, 16 Jul 2014 06:03:08 GMT

Hi,

Try this expression

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

Regards,

jagan.

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

uacg0009 — Wed, 16 Jul 2014 07:14:00 GMT

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!

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

jagan — Wed, 16 Jul 2014 07:34:23 GMT

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.

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

uacg0009 — Wed, 16 Jul 2014 07:37:24 GMT

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?

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

uacg0009 — Wed, 16 Jul 2014 09:45:10 GMT

Anyone can help me with that?

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

preminqlik — Wed, 16 Jul 2014 15:49:09 GMT

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

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

jagan — Thu, 17 Jul 2014 01:56:36 GMT

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.

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

uacg0009 — Thu, 17 Jul 2014 03:40:25 GMT

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 ?

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

preminqlik — Thu, 17 Jul 2014 16:10:46 GMT

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

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

uacg0009 — Fri, 18 Jul 2014 02:03:27 GMT

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

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

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

preminqlik — Sat, 19 Jul 2014 07:31:13 GMT

try the below exp ..slightly changed

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

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

uacg0009 — Mon, 21 Jul 2014 04:27:50 GMT

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?

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

uacg0009 — Tue, 22 Jul 2014 02:05:16 GMT

Anyone can help me with this problem??

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

preminqlik — Tue, 22 Jul 2014 23:48:34 GMT

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 ..

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

preminqlik — Wed, 23 Jul 2014 01:13:37 GMT

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)

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

uacg0009 — Fri, 25 Jul 2014 03:38:35 GMT

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?

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

preminqlik — Fri, 25 Jul 2014 11:11:08 GMT

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.

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

mambi — Fri, 10 Nov 2017 10:21:07 GMT

I know it been awhile but better late than never :

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

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

vinieme12 — Fri, 10 Nov 2017 11:01:11 GMT

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)

)