Divide through with condition

[JPanzer]

Hi,

Í got a stock sum on a monthly base. But, since some shops dont work very accurate, they dont deliver those nombers every month.

So, normally, to calculate the average, I would sum up the stock numbers and devide the result through the number of months.

How can I devide through the number of months, in which we have numbers?

Thanks in advance

[hic]

You need to calculate Sum(Stock)/Count(distinct Month) per shop.

This is best done using an Aggr call:
Sum(Aggr(Sum(Stock)/Count(distinct Month),Shop))

[JPanzer]

Yes, but the problem is, i want to exclude the months with no data:

lets say there are only numbers for months 1 2 4 8 9, then i would like to devide through 5

[hic · Accepted solution]

Yes, and that's what will happen in this expression.

The "Sum(Stock)/Count(distinct Month)" will be calculated individually for each shop, then summed. If there are only 5 months associated with shop X, then "Count(distinct Month)" will be 5 for that shop.

Should you have an association between a Shop and an unreported Month - which isn't likely - then you will have NULL in the Stock field for that combination, and can use the following instead:

Sum(Aggr(Sum(Stock)/Count({<Stock={"*"}>} distinct Month),Shop))

[JPanzer]

Thanks, I needed the second Formula, since there were zeros in the months

[JPanzer]

Now i have the problem, if there is the number zero in the empty months, it doesnt work correctly with your solution...

Is there a way to ignore the months with the zero in it?

Edit: Nevermind, I just had to change the Stock={"*"} to Stock -= {0}