Qlik Community

Qlik Sense Cloud Discussions

Highlighted
maurizio_masini
New Contributor II

Sum Hrs in a period and divide by N. of Active resources

Thanks to whoever migh have tested already something and is willing to share!

My data is like in table (A) here:

NAMESNAIAFROMIFROM
AAAAAAAAASA01/01/2018
BBBBBBBBBSA01/07/2018
CCCCCCCCNA01/04/2018
DDDDDDDDNI01/04/201801/09/2018
EEEEEEEEESI01/01/201801/06/2018

SN = South/North, a flag needed

AI = Active or Inactive

AFROM: Active from

IFROM = Inactive from

I have another table (B) for hours by date:

DATESN
01/01/20181015
01/02/2018119
01/03/20181212
01/04/20181011
01/05/20181314

Where the S and N have the same meaning

I have also 2 factors. S hours are good when sum > 160 month, sum of N hours are enough to be > 128 month.

I would like to calculate in a formula (the first table is manually entered) the SUM of hrs for a month in table B, aggregated by SN and then divided by the number of Active names in that month and verify if OK or not.

Cannot recreate it in QS formula...

thanks !

1 Solution

Accepted Solutions
timpoismans
Valued Contributor

Re: Sum Hrs in a period and divide by N. of Active resources

Correct me if I'm wrong, but if I look at the Excel formula, you just need to make a calendar table in the script and then do the following in a view:

1. Create a table with dimension Year and Month (Can be pivot or straight, however you want)

2. Add the formula: IF( ((160-SUM(S)) + (128-SUM(N)) ) < 0 , 'OK', 'NOTOK')


If you divide a negative number by a positive number (your active employees should always be a positive number), you always get a negative number in return. So you don't need to divide through the active amount of employees if you're just checking if the value is negative or not.

8 Replies
timpoismans
Valued Contributor

Re: Sum Hrs in a period and divide by N. of Active resources

Correct me if I'm wrong, but if I look at the Excel formula, you just need to make a calendar table in the script and then do the following in a view:

1. Create a table with dimension Year and Month (Can be pivot or straight, however you want)

2. Add the formula: IF( ((160-SUM(S)) + (128-SUM(N)) ) < 0 , 'OK', 'NOTOK')


If you divide a negative number by a positive number (your active employees should always be a positive number), you always get a negative number in return. So you don't need to divide through the active amount of employees if you're just checking if the value is negative or not.

maurizio_masini
New Contributor II

Re: Sum Hrs in a period and divide by N. of Active resources

I might have oversimplified the Excel formula.

Forgetting what I typed (removed now from the original post), the concept is:

- Tell me if the n. of hrs recorded is enough to cover a month of work (160hrs typically) by a Team of x people.

Oversimplifying, if in a month the Team of 7 works 2000 hrs (I said oversimplify!), I'm good (2000/7 = 285). If instead they work 1000 hrs, I am not (1000 / 7 = 140+/-).

Not so straightforward, as I need unfortunately to take in account the fact that for some type of Team member I am good if I cover 80% of 160hrs, while for other I need 100% cover. And the n. of resources in a Team might vary per month.

Who effectively worked (N or S) the hrs is not important, eventually is the equation that is. That's why I might not consider the details of how many hrs were effectively worked by a S or a N in table (b)

Apologies for the confusion created by the formula... and thanks!

timpoismans
Valued Contributor

Re: Sum Hrs in a period and divide by N. of Active resources

A few questions so I can understand the issue a bit better:

  1. Date format is DD/MM/YYYY or MM/DD/YYYY?
  2. Do you want to take members into account that are inactive (AI=I), but were active in the month you are looking at.
    1. To clarify: I want to check if February was OK for Team South. Member A is active, is part of Team South. Member E is not active, is part of Team South, but was active in February.
    2. So when calculating if February was OK, do you take Member A or Member A AND Member E?
maurizio_masini
New Contributor II

Re: Sum Hrs in a period and divide by N. of Active resources

Format is dd/mm/yyyy and the assumptions you made are correct.

That's why i need to consider the date: someone not active today was maybe last month. As I tried to explain, I used this consideration to balance without getting in too much unnecessary details (don't need to know if 2000 hrs were covered 50/50 S/N or 80/20. But the total of hrs accumulated and it is divided among all members independently from origin.

timpoismans
Valued Contributor

Re: Sum Hrs in a period and divide by N. of Active resources

Hi Maurizio

I still might have misunderstood the issue at hand, but take a look at the following:

Sum_Hours.PNG

The two upper tables are data tables, the bottom table is, hopefully, your way to a solution.

I haven't taken S-hours or N hours into account, just the total divided by active workers that month.

If a workers was inactive from 1/6/2018, I don't count him as active for June.

timpoismans
Valued Contributor

Re: Sum Hrs in a period and divide by N. of Active resources

Hey maurizio.masini

Have you been able to solve the issue or is there anything else we can help with?

Regards,

Tim P.

maurizio_masini
New Contributor II

Re: Sum Hrs in a period and divide by N. of Active resources

Hi time, yes, indeed that helped a lot! So thanks. I had to adjust the formulas, just to make them a one liner (and then apply as colors eventually, but the idea was excellent.)

Again, thanks for your time!

timpoismans
Valued Contributor

Re: Sum Hrs in a period and divide by N. of Active resources

Good to hear!

And of course, happy to help.

If your question is resolved, please mark the answer (even if it's your own) as the correct one.

That way we can avoid leaving the question up as "Unanswered".

Regards,

Tim P.