Thanks to whoever migh have tested already something and is willing to share!
My data is like in table (A) here:
NAME | SN | AI | AFROM | IFROM |
---|---|---|---|---|
AAAAAAAAA | S | A | 01/01/2018 | |
BBBBBBBBB | S | A | 01/07/2018 | |
CCCCCCCC | N | A | 01/04/2018 | |
DDDDDDDD | N | I | 01/04/2018 | 01/09/2018 |
EEEEEEEEE | S | I | 01/01/2018 | 01/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:
DATE | S | N |
---|---|---|
01/01/2018 | 10 | 15 |
01/02/2018 | 11 | 9 |
01/03/2018 | 12 | 12 |
01/04/2018 | 10 | 11 |
01/05/2018 | 13 | 14 |
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 !
Solved! Go to Solution.
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.
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.
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!
A few questions so I can understand the issue a bit better:
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.
Hi Maurizio
I still might have misunderstood the issue at hand, but take a look at the following:
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.
Hey maurizio.masini
Have you been able to solve the issue or is there anything else we can help with?
Regards,
Tim P.
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!
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.