Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

really need help pls with looking at table before

4 hours ago

I got the following code

Arriva FLEX instroom = 
var test =if(DATEDIFF(RELATED('Dim medewerker'[Indienstdatum]), RELATED('Dim medewerker'[Uitdienstdatum]), MONTH) = 0 && RELATED('Dim medewerker'[Reden verlaten baan]) = BLANK(), 0,1)
return
IF ( 
   'Fct medewerker logboek'[Aantal fte] = 0 && RELATED('Dim medewerker'[Indienstdatum]) = 'Fct medewerker logboek'[Datum logboek],
    VAR CurrentEmpSince = RELATED('Dim medewerker'[Indienstdatum])
    VAR CurrentOutSince = RELATED('Dim medewerker'[Uitdienstdatum])
    VAR CurrentEmpTable = 
            FILTER ( 
            CALCULATETABLE ( 'Fct medewerker logboek', ALLEXCEPT ( 'Fct medewerker logboek', 'Fct medewerker logboek'[Medewerker nummer]) ),
            'Fct medewerker logboek'[Aantal fte] = 0
        )
    VAR TableBefore = FILTER ( CurrentEmpTable, RELATED('Dim medewerker'[Indienstdatum]) < CurrentEmpSince )
    VAR PreviousOutSince = MAXX ( TableBefore, RELATED('Dim medewerker'[Uitdienstdatum]))
    RETURN
    IF(test = 1,
    IF ( 
            INT ( CurrentEmpSince - PreviousOutSince ) > 3 || PreviousOutSince == BLANK(),
            1
        )
))

 it calculates when someone entering the company, but on some conditions, one condition is that this code only calculates people entering the company with a zero hour contract and that it only can enter when it is his/her first 0 zero hour contract or if their last contract was more than 3 days before their new contract, see above

but I need help cuz I try to calculate this same calculation but without the given "location" is "Leiden Bijhuur" or "Lisse Bijhuur". I will show you example from what this code will do now and what I want it to do:
current code example:

EmployeeEmployee numberentering dateleaving datereason leavinghours_contract(fte)locationCurrent code result(name: entering)
Peter11-1-201930-06-2019- 0.9Leiden 
Peter11-7-201931-7-2019-0Leiden Bijhuur1
Peter 11-8-201931-12-2019-0Leiden 


another example

EmployeeEmployee numberentering dateleaving datereason leavinghours_contract(fte)locationCurrent code result(name: entering)
Danny22-2-20202-7-2020-0Lisse1
Danny218-8-202018-3-2021-0Lisse1
Danny219-3-202131-4-2021-0Lisse Bijhuur 
Danny21-5-2021 -0Lisse

 

So the problem is that it sees lisse bijhuur en leiden bijhuur as a row and I dont want it to see it as a row
the following example should be the end result

EmployeeEmployee numberentering dateleaving datereason leavinghours_contract(fte)locationCurrent code result(name: entering)
Peter11-1-201930-06-2019- 0.9Leiden 
Peter11-7-201931-7-2019-0Leiden Bijhuur 
Peter 11-8-201931-12-2019-0Leiden1

and

EmployeeEmployee numberentering dateleaving datereason leavinghours_contract(fte)locationCurrent code result(name: entering)
Danny22-2-20202-7-2020-0Lisse1
Danny218-8-202018-3-2021-0Lisse1
Danny219-3-202131-4-2021-0Lisse Bijhuur 
Danny21-5-2021 -0Lisse

1

the question is how can I make this code work that it doesnt see locations as "lisse bijhuur" and "leiden bijhuur"
someone already helped me with this code, I made little changes, but pls someone help me cuz it is really difficult and I cant get it work like how I want it

No Replies