0

Is this lookup, group and sum possible?

 

I am trying to calculate the total people working over a two year period. We have lots of contractors and need to create a graph of the total people working for us over the next 2 years.

I have managed to get the months for the next two years without a problem, however trying to get the series is driving me a little crazy. I think its because i need to group not only the start and end dates but summing the total working times, and i just cant get my head around it.

The red line is what i'm trying to achieve.

And this is the data I'm working with. Any ideas on how I should be going about this?

 

This is what i managed to get working to display the blue line on the above, however i'm not sure why it chose that month, nor why it isn't working.

M is Calc_StartDate2
N is Calc_FinishDate2
G is WorkingTime

Any help/ideas would be greatly received.

1 comment

  • 0
    Avatar
    Keely Davison

    Hi Gordon,

    This scenario is a bit complex and I would use a MAP function as follows.  You may need a few tweaks for your specific cases.

    MAP (&x Axis: Months, "Month", 
                        SUM( IF ( AND( DATE ($Month, "MMM-yyyy")>= DATE(@M:M,"MMM-yyyy"), DATE ($Month, "MMM-yyyy")<= DATE(@N:N,"MMM-yyyy")),
                                         @G:G, BLANK())))

    This formula will take each value on the x-axis, assign the value to a variable called "Month" and execute the formula in the 3rd parameter of the MAP function.  The 3rd parameter is doing your logic to check if the month is within the start and end range for each employee and getting the value in G if it is.

    When using the MAP function you need to create a variable.  In this example I have called it "Month" and refer to it in the 3rd parameter using a $ prefix.  $Month.  You can name the variable what ever you want.  

    One final note, to do date comparisons you need to convert the dates to unix time and we use the DATE function for that, so you can see in the formula the DATE function is around all date references. 

    Below is information on the MAP function, working with variables, and the DATE function.  As your scenario is a bit complex please do not hesitate to reach out to support via ticket by emailing support@klipfolio.com and we can help you more specifically there.

    https://support.klipfolio.com/hc/en-us/articles/360011416893-Klipfolio-functions?query=functions#MAP

    https://support.klipfolio.com/hc/en-us/articles/360011416893-Klipfolio-functions?query=functions#DATE

    https://support.klipfolio.com/hc/en-us/articles/215547108-Variables

    Hope this helps,

    Keely

Please sign in to leave a comment.