topic Re: Totals based on Min & Max in QlikView

Totals based on Min & Max

Anonymous — Thu, 28 Jul 2016 09:57:38 GMT

I have a table (below), and I want to calculate the turnover of staff using the Min & Max Month. I have highlighted the data I need to show as a summary

Start_Month	Staff in post at start of Month	Staff in post at end of Month	Avg Month Staff Count	Sum(Leavers)
01/01/2015	18	19	18.5	1
01/02/2015	19	18	18.5	0
01/03/2015	18	19	18.5	0
01/04/2015	19	17	18	1
01/05/2015	17	17	17	0
01/06/2015	17	15	16	0
01/07/2015	15	17	16	0
01/08/2015	17	17	17	0
01/09/2015	18	20	19	0
01/10/2015	20	23	21.5	1
01/11/2015	22	21	21.5	0
01/12/2015	21	22	21.5	0

I want to show the following as a table. How do I do it ?

Staff in post at start of Month	Staff in post at end of Month	Avg Month Staff Count	Min_Month	Max_Month	Leavers	Turnover (Leavers/Avg Month Staff Count)
18	22	20	01/01/2015	01/12/2015	3	15%

Any help appreciated?

Regards

Phil

Re: Totals based on Min & Max

jonathandienst — Thu, 28 Jul 2016 09:59:52 GMT

Do you have a field in your source for Leavers, or are you calculating it? How are you setting min and max months?

Re: Totals based on Min & Max

Anonymous — Thu, 28 Jul 2016 10:04:11 GMT

Yes there is a field for leavers, and I have calculated the min / max by creating an expression min(Start_Month) / Max(Start_Month), then used the selection to determine start and end points.

Hope that makes sense

Re: Totals based on Min & Max

Anonymous — Thu, 28 Jul 2016 10:05:29 GMT

I mean (Max(End_Month)

Re: Totals based on Min & Max

Anonymous — Thu, 28 Jul 2016 10:45:11 GMT

use following expression in the pivot table without any DIMENSION

Sum({$<[Start_Month]={"$(=Date((Min(Start_Month)), 'MM/DD/YYYY'))"}>} [Staff in post at start of Month] )

Sum({$<[Start_Month]={"$(=Date((Max(Start_Month)), 'MM/DD/YYYY'))"}>}[Staff in post at end of Month] )

=Date((Max(Start_Month)), 'MM/DD/YYYY')

=Date((Min(Start_Month)), 'MM/DD/YYYY')

sum([Sum(Leavers)])

and about this metric :

Avg Month Staff Count : 20

I am getting as 18.8 ............please check and confirm the calculation for this field ?

Re: Totals based on Min & Max

Anonymous — Thu, 28 Jul 2016 12:06:46 GMT

Slight adjustment needed for UK dates, but worked well.

In regards to the Avg headcount. The average in a total of start count (18) & end count (22) then divided by 2

18+22 = 40

40 / 2 = 20

Re: Totals based on Min & Max

Anonymous — Thu, 28 Jul 2016 12:07:12 GMT

thanks BTW