How to calculate?

[#1]

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.

[johnw]

firstsortedvalue(VisitMinutes,Visitdate)

See attached.

[#3]

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

[#4]

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.

[johnw]

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.