6 Replies Latest reply on Nov 22, 2016 3:13 AM by Leslie Raillon

# Make average on an aggregate

Hello all,

I have an aggregated based data (based on formula countd(device_id)).

And I would like to do a month average... any ideas?

Thanks a lot

Regards

Leslie

• ###### 1. Re: Make average on on aggregate

Hi Leslie,

Are you looking anything like in pic:

• ###### 2. Re: Make average on on aggregate

Hi,

Sure looks good. Thanks.

I can't seem to find "cntd" formula... any reasons?

Thanks

Regards

Leslie

• ###### 3. Re: Make average on on aggregate

The formula is COUNTD. Pill uses a shortened label not to take up too much space.

• ###### 4. Re: Make average on an aggregate

Hi

As mentioned in my first message, I already uses countd and that calculates a SUM.

I loaded a new document "Book 2" to etter help on what I need.

--> In Book 2 you have by product sum of activations per month....

Let's imagine you have several years: how would I calculate an average per month of activations?

Thanks a lot

Best regards,

Leslie

• ###### 5. Re: Make average on an aggregate

You can do this with LOD calcs or table calcs. Since your example workbook only has 1 year of data, I will explain the process.

The following LOD expression will take all the dimensions in the sheet and add year of Date to it and compute the COUNTD for each.

{include YEAR([Date Setup]): COUNTD([Device Id])}

So you will get the countd for january 2010 for Product1, january 2011 for Product 1, februrary 2011 for Product 2 etc.

Now I am understanding correctly you want to roll this up to get the average across the years.

e.g. avg of countd(device id) for january, Product 1

You can do this by simply wrapping the calculation in AVG

AVG({include YEAR([Date Setup]): COUNTD([Device Id])})

since your cross-tab in the example workbook only has month and product in it. This will average across years to give you one value per month, product.

• ###### 6. Re: Make average on an aggregate

Hi

Great thanks a lot!