calculating sum of a field based on its first log date, but ignore selection.

[#1]

Hello,

I need to calculate the sum of time, for a said, product and version for the min(date) for that product, version

I tried achieving this using aggr() but when a different date is selected in the multibox, the values seem to change.

Against each product,version - I want the sum(time) belonging to the first date for that product and version.

If a different date is selected in multibox, this value should not change.

How ever selection on product and version on multibox can change the straight table

Date        Product        Version        Time       

1/1/2011    chocolate        1.1        25   

1/1/2011    chocolate        1.1        30

1/1/2011    chocolate        1.1        13

1/1/2011    chocolate        1.2        51

1/1/2011    chocolate        1.2        26

1/18/2011    chocolate        1.1        23

1/18/2011    chocolate        2.1        32

1/18/2011    chocolate        2.1        24

1/18/2011    chocolate        1.2        54

1/1/2011    candy        1.1        35   

1/1/2011    candy        1.1        30

1/1/2011    candy        1.1        13

1/1/2011    candy        1.2        51

1/1/2011    candy        1.2        16

1/18/2011    candy        1.1        23

1/18/2011    candy        2.1        13

1/18/2011    candy        2.1        24

1/18/2011    candy        1.2        54

Need to calculate

sum of time for each version the first time it was intoduced.

chocolate        1.1    first entry on 1/1/2011 so sum of all chocolate 1.1 as of 1/1/2011 - 68 (25+30+13)

chocolate        1.2    first entry on 1/1/2011 so sum of all chocolates 1.2 as of 1/1/2011 - 77 (51+26)

chocolate        2.1    first entry on 1/18/2011 so sum of all chocolates 2.1 as of 1/18/2011 - 56 (32+24)

candy        1.1    first entry on 1/1/2011 so sum of all candy 1.1 as of 1/1/2011 - 78 (35+30+13)

candy         1.2    first entry on 1/1/2011 so sum of all candy 1.2 as of 1/1/2011 - 67 (51+16)

candy        2.1    first entry on 1/18/2011 so sum of all candy 2.1 as of 1/18/2011 - 37 (24+13)

[#2]

Hi Geetha ,

Correct me know if i got u wrong

can you check the attached qvw

Thanks

Meher

[#3]

Hi Merwan

Thanks for responding, the current formula that you have provided

Sum({<Date=$(min(Date))>}Time)

take the min of Date column, but what happens is when I select Candy and 1.1 in the multibox

sum(mindate_time) displays = 101

which includes 1/18/2011 value - 23

This value should only display = 78 (which are the values for 1/1/2011)

Could you please take a look

[#4]

Use the expression sum( {$<Date = {'$(=Date(Min(Date)))'}>} Time )

[qliksus]

Hi geetha,

try this expression

sum({1}aggr(

if( sum({1}aggr(count({1}DISTINCT Date),Product,Version))  >'1' , sum({1}if(Date= min({1}total Date) ,Time)) )

,Product,Version,Date )) +

sum({1}aggr(

if( sum({1}aggr(count({1}DISTINCT Date),Product,Version))   ='1' ,

sum({1}if(Date= max({1}total Date) ,Time))), Product,Version,Date ))

Its working for me