Qlik Community

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_MonthStaff in post at start of MonthStaff in post at end of MonthAvg Month Staff CountSum(Leavers)
01/01/2015181918.51
01/02/2015191818.50
01/03/2015181918.50
01/04/20151917181
01/05/20151717170
01/06/20151715160
01/07/20151517160
01/08/20151717170
01/09/20151820190
01/10/2015202321.51
01/11/2015222121.50
01/12/2015212221.50

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

    

Staff in post at start of MonthStaff in post at end of MonthAvg Month Staff CountMin_MonthMax_MonthLeaversTurnover (Leavers/Avg Month Staff Count)
18222001/01/201501/12/2015315%

Any help appreciated?

Regards

Phil

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

and about this metric :

Avg Month Staff Count : 20


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

View solution in original post

6 Replies
Highlighted
MVP
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)])

and about this metric :

Avg Month Staff Count : 20


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

View solution in original post

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

Re: Totals based on Min & Max

thanks BTW