Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
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))
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
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))
Thanks, I needed the second Formula, since there were zeros in the months
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}