6 Replies Latest reply on Jun 25, 2012 4:08 PM by Jess Lee

# sum of window_min?

Hello,

Can anyone please help me find min data group by top id and middle id ? Then i would like to sum all min data to get final result.  The final result will show only \$137.59 nothing else.  Many thanks

 Data TOP ID MIDDLE ID DETAIL ID Amt Need to create a Tableau calculated field to have below results 100290104 100130103 12050215412 \$25.46 \$25.46 100290114 100130104 12050215413 \$7.57 \$7.57 100290153 100152068 1206081802 \$50.00 100290153 100152068 12061205115 \$24.96 \$24.96 100290353 100130063 1205020001 \$7.81 100290353 100130063 12050315410 \$7.81 \$7.81 100290357 100130064 1205020002 \$12.45 100290357 100130064 1205031602 \$9.34 \$9.34 100290364 100130065 1205020004 \$62.45 100290364 100130065 1205031609 \$62.45 \$62.45
• ###### 1. Re: sum of window_min?

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.

• ###### 2. Re: sum of window_min?

wow... this is exactly what i'm looking for.... Thank you so much

• ###### 3. Re: sum of window_min?

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?

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?

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.

1 of 1 people found this helpful
• ###### 6. Re: sum of window_min?

Thank you so much Joe.  Greatly appreciated