topic Re: average month chart in QlikView

average month chart

shilina_klara — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: average month chart

effinty2112 — Wed, 18 Oct 2017 11:19:20 GMT

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

Re: average month chart

shilina_klara — Wed, 18 Oct 2017 11:32:45 GMT

effinty2112

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

Re: average month chart

shilina_klara — Mon, 23 Oct 2017 05:48:30 GMT

Thank you all, I decided my question!