4 Replies Latest reply on Oct 3, 2018 9:54 AM by marc de Latour

# Finding average of aggregated fields, while hiding LOD- Fixed - CountD

good afternoon,

I am having some issues.

I have included the sample store data for reference.  keep in mind its derivative on my issue so you may find your self wondering why he is doing this, it doesn't make much sense with sample store data, but for my issue it does.

1) i'd like to divide these two numbers,  fixed sum of sales by state and date. and countD of cities. for an average sales for region by city.

2) I would also like to get a similar number for the average of the "difference from" for state by city ...any help would be appreciated

• ###### 1. Re: Finding average of aggregated fields, while hiding LOD- Fixed - CountD

HI Marc,

Not sure why, but your 'Calculation 3' seemed to need a corresponding LOD as the denominator.

I changed this to an EXCLUDE of OrderID in the attached.

Problem 2 seems ok now as well.

Thanks,

Jonathan

1 of 1 people found this helpful
• ###### 2. Re: Finding average of aggregated fields, while hiding LOD- Fixed - CountD

Hi Marc

you had a couple things going on with the calculation 3

you were getting an error because countd is an aggregate but your LOD was not aggregated

Further you need to fix (using an LOD) the count of the cities in the state - I created the Ratio as

and it returns this

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
• ###### 3. Re: Finding average of aggregated fields, while hiding LOD- Fixed - CountD

You are getting me there...

one final question and either way you provided me my answer.

how would i return a single value =  name of the city with

a) max sales

b)min sales

c)max sales growth

d)min sales growth

I'm trying to create a "Insights care" with KPI's

• ###### 4. Re: Finding average of aggregated fields, while hiding LOD- Fixed - CountD

You are getting me there...

one final question and either way you provided me my answer.

how would i return a single value =  name of the city with

a) max sales

b)min sales

c)max sales growth

d)min sales growth

I'm trying to create a "Insights care" with KPI's