
1. Re: sum of window_min?
Joe Mako Jun 20, 2012 10:27 PM (in response to Jess Lee)How about the attached?
The field "Min Amt" returns the lowest value for for each id combination, partitioned on top and middle because it uses detail for addressing and the detail pill is sorted ascending. The "Sum of Min" field sums those values with no partitioning, providing the overall sum of min values.

Sum of Min.twbx.zip 31.7 KB


2. Re: sum of window_min?
Jess Lee Jun 21, 2012 8:17 AM (in response to Joe Mako)wow... this is exactly what i'm looking for.... Thank you so much

3. Re: sum of window_min?
Jess Lee Jun 21, 2012 9:09 AM (in response to Jess Lee)Hi Joe,
One more thing, is there any way i can calculate the avg amt and display right next to overall sum of min?
Avg amt = overall sum of min / count distinct of middle id = 137.6 / 6 = 22.8
Thanks again

4. Re: sum of window_min?
Jess Lee Jun 22, 2012 7:37 AM (in response to Jess Lee)Hello,
Does anyone know if it is possible to calculate the avg amt?
Avg amt = overall sum of min / count distinct of middle id = 137.6 / 6 = 22.8
thank you

5. Re: sum of window_min?
Joe Mako Jun 22, 2012 2:42 PM (in response to Jess Lee)Sorry for the delay,
What you are asking for is an average of the min values, and that can be done by changing the formula from
IF FIRST()==0 THEN
WINDOW_SUM([Min Amt],0,IIF(FIRST()==0,LAST(),0))
END
to
IF FIRST()==0 THEN
WINDOW_AVG([Min Amt],0,IIF(FIRST()==0,LAST(),0))
END
with the same compute using setup for the nested table calcs, as in the attached.

Sum and Avg of Min.twbx.zip 33.0 KB


6. Re: sum of window_min?
Jess Lee Jun 25, 2012 4:08 PM (in response to Joe Mako)Thank you so much Joe. Greatly appreciated