Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

philmywallet
Not applicable

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
galax_allu
Not applicable

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 ?

6 Replies
jontydkpi
Not applicable

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
philmywallet
Not applicable

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

philmywallet
Not applicable

Re: Totals based on Min & Max

I mean (Max(End_Month)

galax_allu
Not applicable

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 ?

philmywallet
Not applicable

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

philmywallet
Not applicable

Re: Totals based on Min & Max

thanks BTW