12 Replies Latest reply on Mar 13, 2018 10:27 AM by Jim Dehner

# Window sum field with Fixed : {}

Hello,

I have 2 fields where i calculated using window sum :   "[3M_OT])" and [3M_PERFORMANCE]

I need to get the below formula to work in order to use this field to count the group for each category.

I have the error in the screenshot.

This is the formula :

{FIXED [Supplier Id],[Airline Id],[Rated],[Repairs Spares],[Group],[Period Date]:

IF [Parameter 1]="Current" AND

SUM([3M_OT])/SUM([3M_PERFORMANCE])>=0.95 THEN "GREEN"

ELSE "RED" END}

Please let me know how can i get around this error.

I am sorry but i really can't upload any worksheets. Would appreciate your help if possible, thanks.

Regards,

Tarek

• ###### 1. Re: Window sum field with Fixed : {}

Good morning

Window_sum is a table calculation and table calculations are close to the last operation in the order of operations

well past all standard calculations or LOD's

so you not be able to use the approach you are suggestioning

That said you can use other table calculations like window_count  on your table calc or possibly find a way around using window_sum in you initial calculation

if you post your TWBX workbook I will try to help

JIm

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Window sum field with Fixed : {}

Hi Jim ,

Thank you for your message. I have attached sample for you.

My window calculation field is the "3M_Performance" for 3 Month rolling calculation.

My goal is to count how many are in "green" , "yellow" and "red" according to the Color_Performance field

I am looking for something similar to the below screenshot :

• ###### 3. Re: Window sum field with Fixed : {}

Hi I need some help here - what is the dimension that you are actually counting?  I can't see anything that is going to return 3/3 in january and 7 in february

Jim

• ###### 4. Re: Window sum field with Fixed : {}

Hi Jim,

Sorry for the misunderstanding. The above screenshot is just for the look of the report i want to create. The numbers in the image are not related to the data in the workbook. Thanks again

• ###### 5. Re: Window sum field with Fixed : {}

I am counting the performance

if [3M_PERFORMANCE]>=0.9 then "GREEN"

ELSEIF [3M_PERFORMANCE]>= 0.5 then "YELLOW"

ELSE "RED" END

I want to know how many "Count" were in Green, Yellow and RED according to the "3M_PERFORMANCE" which is the 3 Month rolling average performance. I hope that makes sense. Thank you

• ###### 6. Re: Window sum field with Fixed : {}

thanks for the clarification

now bear with me - the performance is related to something - is the performance at the "Code" level or the Key level or a combination of the 2

Jim

• ###### 7. Re: Window sum field with Fixed : {}

Sure thanks for your help. The performance is related to both code and key.

The performance is the

SUM ("OK")/ SUM(TOTAL)

• ###### 8. Re: Window sum field with Fixed : {}

see the attached

I'm not totally convinced it is producing the right answers so check it carefully

here is the result

The formulas are all your original ones

the table calculations are across

I put the date in the context so you could show or hide the months either manually or dynamically

I would change the colors but that is just a personal thing

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 9. Re: Window sum field with Fixed : {}

Great almost there Jim, but one last thing please, in your solution it shows multiple green , yellow or red sectors. How can i combine these for each bar in order to give me total for each color group at each month.

For example :

Jan :  2 Red 1 Green

Feb: 3 Green

• ###### 10. Re: Window sum field with Fixed : {}

I am not sure but see the attached

I am confused on your keys and codes that are what is being plotted

this is what happens when I swap out keys of r codes and do all the table calculations across keys

Jim

1 of 1 people found this helpful
• ###### 11. Re: Window sum field with Fixed : {}

That did it , Awesome, Thank a lot Jim, i appreciate your support.

Tarek