topic How to calculate? in QlikView

How to calculate?

Fri, 11 Feb 2011 10:29:48 GMT

Hi,

I have those figures:

MemberId, Visitdate, VisitMinutes

1 ,2010-1-1,10

1,2010-1-2,11

2,2010-1-1,10

2,2010-1-3,10

I want to use pivot table to show the visit minutes of the first date by MemberId

It looks like this:

MemberId, TotalMins, FirstMins

1,21,10

2,20,10

How to use expression to calculate the First Mins?

Thanks.

How to calculate?

johnw — Sat, 12 Feb 2011 02:09:16 GMT

firstsortedvalue(VisitMinutes,Visitdate)

See attached.

How to calculate?

Sat, 12 Feb 2011 13:42:23 GMT

Sorry, I used named cal, could paste the code directly?

How to calculate?

Sun, 13 Feb 2011 06:03:04 GMT

Thanks for your reply,

But if I have another dimension upon the memberid like region,

load * Inline
[Region,MemberId,VisitDate,VisitMins
Brazil,1,2010-2-1,10
Brazil,1,2010-2-2,20
Brazil,2,2010-2-2,20
France,3,2010-2-1,3
France,4,2010-2-3,5
]

I want to show the total visit minutes of the first date by MemberId on the region dimension:

It looks like this:

Region, totalMins, TotalFirstMins

Brazil, 50, 10+20

France, 8, 3+5

How to achieve it?

Thanks.

How to calculate?

johnw — Mon, 14 Feb 2011 20:39:46 GMT

sum(aggr(firstsortedvalue(VisitMins,VisitDate),Region,MemberId))

The script is cut and paste from your post. The chart is a straight table with Region as a dimension. First expression is sum(VisitMins). Second is the expression above.

Edit: Oh, and in the real world, you'll want to use a VisitTimestamp instead of a VisitDate unless it is physically impossible for a member to have two visits on the same date.