# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Contributor

## Totals based on Min & Max

Hi

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

Tags (4)
1 Solution

Accepted Solutions
Highlighted
Valued Contributor

## Re: Totals based on Min & Max

Hi

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)])

Avg Month Staff Count : 20

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

6 Replies
Highlighted
MVP

## Re: Totals based on Min & Max

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Contributor

## Re: Totals based on Min & Max

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

Highlighted
Contributor

## Re: Totals based on Min & Max

I mean (Max(End_Month)

Highlighted
Valued Contributor

## Re: Totals based on Min & Max

Hi

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)])

Avg Month Staff Count : 20

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

Highlighted
Contributor

## Re: Totals based on Min & Max

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

Highlighted
Contributor

thanks BTW