Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello! Sorry for my English
Faced such a problem:
I need to count the average number of employees of the company for each month. In Excel, it looks like this:
January | February | March | April | |
---|---|---|---|---|
Active population | 1000 | 2000 | 3000 | 4000 |
The formula is = | AVG (Sum of Active strength in JANUARY, Sum of Active strength in FEBRUARY) | = AVG (AVG (Amount in JANUARY, Sum in FEBRUARY), AVG (Sum in FEBRUARY, Amount in MARCH)) | = AVG (AVG (Sum in FEBRUARY , Amount in MARCH), AVG (Amount in April, Amount in APR)) | = AVG (AVG (Amount in March, Amount in APR), AVG (Amount in APR, Amount in MAY)) |
SSH | AVG (1000,2000) = 1500 | AVG (AVG (1000,2000), AVG (2000,3000)) = 2000 | 3000 | 3750 |
And you should get an ordinary histogram for the months with the values of the SSH
Help solve this problem. which formulas just did not try, but he does not even register the amount for the next month
Sum ({$ <Month = {"$ (= Date (AddMonths (Month, 1), 'DD.MM.YYYY'))"}, Symptom = {'PHOT'}}} [Active population])
I will be very grateful!
Hi Клара,
I loaded this into QV:
Data:
LOAD * INLINE [
Month, Active population
January, 1000
February, 2000
March, 3000
April, 4000
];
Then this straight table:
Month | SSH |
---|---|
January | 1500 |
February | 2000 |
March | 3000 |
April | 3500 |
SSH is the expression:
if(RowNo() =1,0.5*Sum([Active population])+0.5*Below(Sum ([Active population])),
if(RowNo()=NoOfRows(),
0.5*Sum([Active population])+0.5*Above(Sum ([Active population])),
0.5*Sum([Active population])+0.25*Above(Sum ([Active population]))+0.25*Below(Sum ([Active population]))))
My figure for April differs from yours because I don't have the data needed from May. Instead I took the average of March and April population.
Apart from the first and last rows your SSH formula simplifies to 0.25*Prev + 0.5*Current + 0.25* Next.
Kind regards
Andrew
Thank you very much for your prompt response.
The fact is that Active population is considered by placing 1 opposite the employee, if it is suitable by condition:
January P1 1
January P2 0
January P3 1
and so on
February P1 0
February P2 1
February P3 1
and so on
March P1 1
March P2 0
and so on
Thank you all, I decided my question!