Creating Annualized Usage using Aggr

[#1]

I'm trying to create a formula to derive the annual usage by our customers for a type of sales order. I need the formula to determine how much we sold a particular product to them, how many months of the year we sold it to them and then figure out that average. Then it needs to simply be multiplied by 12 to get the annual usage. However the only dimension for the chart is the year so I believe I need to use the aggr function in order to get the information I need by customer. Please see the formula below and let me know if you see anything I may be doing wrong.

=sum(if([Order Type] = 'ABC',
(aggr(sum([Invoice Amount]), [Product]+[Customer]) /
aggr(count(distinct month([Invoice Date])),[Product]+[Customer]))
*12))

I am making sure to use only fields in the same table as I know that can cause problems. Any help would be appreciated as I have very little experience using aggr.

[vgutkovsky · Accepted solution]

Create a field in the script called Month so you're not calculating it on the fly. If the dimension is Year then your 3 expressions will be:

(1) Yearly sales: sum({<[Order Type]={'ABC'}>} [Invoice Amount])

(2) # of months that you sold it to them: count({<[Order Type]={'ABC'},Month={"=sum([Invoice Amount])>0"}>} distinct Month)

(3) Average sales: column(1)/column(2)

Regards,