10 Replies Latest reply on Oct 16, 2016 2:38 PM by John Croft

# How to calculate the count of countries in the view ?

In a worksheet,where I have the  sum(profit) VS country. How do I can find the count of countries  above the  AVG (profit) across the table ? Can I do it in the view ?If so, then how to do it ?

Thanks,

Elavar.S

• ###### 1. Re: How to calculate the count of countries in the view ?

Create two calculated field :

1)

Average Profit

//Total Profit divide by  count of state across all state

SUM({SUM([Profit])})/SUM({COUNTD([State])})

2)

Is Above Average ?

//This will check if above or below average and flag accordingly

if SUM([Profit]) > [Average Profit]

Then "Above"

Else "Below"

END

2 of 2 people found this helpful
• ###### 2. Re: How to calculate the count of countries in the view ?

You can also create a calculated field to flag countries above the avg profit by coding 1 if yes and 0 if no. Then sum up the the calculated field. That will give you the count.

• ###### 3. Re: How to calculate the count of countries in the view ?

Its can also be done using reference line in your bar chart. Just right click on your axis and add reference line. Follow the screen shot. Once done you can easily get which are the countries above avg line. For this you don't need to create any calculated field.

But If you wants counts then you can try this table calculation field.

State Counts above Avg. Compute using states/country and from filter select value 1.

Hope this help.

Mahfooj

• ###### 4. Re: How to calculate the count of countries in the view ?

Thanks. I did it as

IF AVG([Profit])< WINDOW_AVG(Avg([Profit]))

Then "True"

Else "False"

END

and found the number of countries above and below avg. I am wondering if we can draw the reference line and can get the count of countries above and below average in the view itself?

2 of 2 people found this helpful
• ###### 5. Re: How to calculate the count of countries in the view ?

I placed the calculated field and got the no of rows as 46 which is equal to the countries below average.

When we use reference line in the worksheet , can we get the no of countries above and below average without any calculated filed ?

1 of 1 people found this helpful
• ###### 6. Re: How to calculate the count of countries in the view ?

Thanks Mahooj for clarifying my doubt one more time.

1 of 1 people found this helpful
• ###### 7. Re: How to calculate the count of countries in the view ?

When we use reference line in the worksheet , can we get the no of countries above and below average without any calculated filed ?

1 of 1 people found this helpful
• ###### 8. Re: How to calculate the count of countries in the view ?

Thank you Sir.

• ###### 9. Re: How to calculate the count of countries in the view ?

Thanks you sir

• ###### 10. Re: How to calculate the count of countries in the view ?

I think you can just use:

AVG([Profit])< WINDOW_AVG(Avg([Profit]))

and that should default to a 'T|F' data type.

It may help with processing in the background if you have lots of data.

And then = 1 or 0 comes in handy to sum up easily to see the aggregate count as a single number. Boolean and numeric data is typically more efficient to process and store.

1 of 1 people found this helpful