10 Replies Latest reply on Nov 12, 2015 9:14 AM by anushachowdary.sakhamuri

# Calculations Help!!

Hi ALL,

I have three Fields "Label, Feat, Char"

Each Label has many feats and each feat has many chars.

For Example:

Label           Feat          Char

abc              001           a001

b001

002            a002

b002

c001

.Even if one Char fails, the entire Label fails

Fail/Pass :

IF [Dev]>[upper limit ]OR [Dev]<[lower limit] THEN "Fail" else "Pass" END

I need a calculation that will give me the failure rate for the Labels.

Thank You!!

• ###### 1. Re: Calculations Help!!

Something like:

SUM( IF [Dev]>[upper limit ]OR [Dev]<[lower limit] THEN 1  END) /

{FIXED [Label] : COUNT([Char]) }

--Shawn

• ###### 2. Re: Calculations Help!!

HI Shawn,

The calculation says cannot mix aggregate and non aggregate values.

• ###### 3. Re: Calculations Help!!

Even if one char fails I want to show that the entire label has failed

• ###### 4. Re: Calculations Help!!

Sorry try:

SUM( IF [Dev]>[upper limit ]OR [Dev]<[lower limit] THEN 1  END) /

SUM({FIXED [Label] : COUNT([Char]) )

• ###### 5. Re: Calculations Help!!

Thank You!

I Created a calculated field called Results

Results: IF [Dev]>[uppel limit] OR [Deviations]<[lower limit] THEN "Fail" else "Pass" END

I am looking for something like

if CONTAINS([Results],"Fail" )THEN [Label]=Fail END

Even if one char fails I want to show that the entire label has failed

Just show if the label has failed or passed

• ###### 6. Re: Calculations Help!!

For Example:

Label           Feat          Char    Results

abc              001           a001     Pass

b001        Pass

002            a002      Fail

b002        Pass

c001        Pass

Since the Char a002 failed the entire label abc Fails.

I want a column that will show label abc = "fail"

• ###### 7. Re: Calculations Help!!

Try:

IF SUM( IF [Dev]>[upper limit ]OR [Dev]<[lower limit] THEN 1  END))  > 0 THEN 'Fail' ELSE 'Pass' END

--Shawn

1 of 1 people found this helpful
• ###### 8. Re: Calculations Help!!

Thank You so much for your help!!

The above calculation shows which labels have passed and which labels have failed.

I have the total number of labels

Now i want to get the count of all the failed labels and the count of all the passed labels.

so that i can calculate the pass percentage and failed percentage.

I called the above calculation as Results  IF SUM( IF [Dev]>[upper limit ]OR [Dev]<[lower limit] THEN 1  END))  > 0 THEN 'Fail' ELSE 'Pass' END

To Get the number of failed labels I tried

SUM (If Results = "Fail" then 1 else 0 end)

Fail Rate:

SUM (If Results = "Fail" then 1 else 0 end) / Total number of labels.

Total Number of Labels = COUNT(IIF(DATEDIFF('day',[L_Date],TODAY())<=6, [Number of Records] , NULL))

(In order to filer each day)

But I am not able to do the SUM since it says its already an aggregation. and If I use Window sum the i am still not getting the right results.

After I get the fail rate and pass rate  I am trying to create a doughnut chart. something like this.

Is there a way to achieve the results.

Thank You

• ###### 9. Re: Calculations Help!!

Got a sample packaged workbook?

• ###### 10. Re: Calculations Help!!

Hi Shawn,

The data is confidential. I don't think I can provide the work book.

I there a way i can get get the total pass and failed labels with pass and fail rate.

Thanks  a lot, appreciate it.