1 2 Previous Next 20 Replies Latest reply on Mar 18, 2019 10:14 AM by Sobia Siddiqui

# Count of calculated field

Hello Everyone,

I need to find number of greens and reds and yellows for period performances.

I need to count how many total greens for each unit combing all four quarters. For instance, 3a should have 3 out of 4, 6E should have 2 out of 4. i want teh sum to show in another column or another table or anywhere. Thank you.

• ###### 1. Re: Count of calculated field

Hello Sobia,

You can use a window count calculation like this. I replicated your problem with superstore. See the attached workbook.

WINDOW_COUNT((if [color] = False then count([Product Name]) end))

Hope this helps!

Robert

1 of 1 people found this helpful
• ###### 2. Re: Count of calculated field

You can use a LOD expression to count the number of green performances.

{FIXED [Unit - Clinical DESC], Quarter : IF [Performance] = "Green" THEN 1 ELSE 0 END }

Below you can see a screenshot of a sample on the SuperStore data source (see also attached)

1 of 1 people found this helpful
• ###### 3. Re: Count of calculated field

Can you help me do that step by step? I created the calculated field. But then where do I put it it to show me the result like you had for sheet 2.

• ###### 4. Re: Count of calculated field

You can just double click the calculated field, or you drag it on the box with the "T"

• ###### 5. Re: Count of calculated field

result of a level of a detail expression must be  an aggregated ERROR

• ###### 6. Re: Count of calculated field

Your calculated field is not an aggregation yet, so you need to use an aggregation. You could use ATTR() to accomplish that.

IF ATTR([Period Performance]) = "Green" THEN 1 ELSE 0 END

• ###### 7. Re: Count of calculated field

Not getting the desired outcome. Tts just added 1 below the numbers. I want to see total greens and total red for each unit on a separate sheet or end of row.

• ###### 8. Re: Count of calculated field

Remove the "Unit" from your Marks card, as well as the Quarter

1 of 1 people found this helpful
• ###### 9. Re: Count of calculated field

When I removed that information I got below results

I want the total of green at the end .

Preferable ( # of green "out of 4)

Output should look like

UNIT NAME  # of green out of # of Quarters

so

3A       4 out of 4

5E       1 out of 4

I am so sorry for the trouble but I really need to get this done.

• ###### 10. Re: Count of calculated field

In my example I added a calculated field

STR(SUM([Number of performances hit])) + " out of " + STR(COUNTD(DATETRUNC('quarter', [Order Date])))

which gives the following result

Is that what you are after?

2 of 2 people found this helpful
• ###### 11. Re: Count of calculated field

YES. exactly! how do I do that for my work?

• ###### 12. Re: Count of calculated field

1. Remove the Quarter from the Columns shelf

2. Create an expression with the above formula (and replace DATETRUNC(...) with Quarter)

That will probably do the trick, although it is hard without the workbook itself

1 of 1 people found this helpful
• ###### 13. Re: Count of calculated field

How do I fix this formula

• ###### 14. Re: Count of calculated field

STR(SUM(IF [Period Performance] = "Green" THEN 1 ELSE 0 END)) + " out of " + STR(COUNTD([Quarter]))

3 of 3 people found this helpful
1 2 Previous Next