Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
Hi Geetha ,
Correct me know if i got u wrong
can you check the attached qvw
Thanks
Meher
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
Use the expression sum( {$<Date = {'$(=Date(Min(Date)))'}>} Time )
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