Sick leave durations per employee

[Douwe_KB]

Hi there,

I am currently trying to implement a function which calculates the sick leave durations for employees. For example if an employee has 3 sick leave periods, I want it to store these periods seperately.

I now have the following code, which does work somewhat but it does not do what I want it to do, as it does not show the sick leave periods seperately. Could anybody help me out? 

TEST:

LOAD *,
     If([Ziekte Status] = 'Ziek' and not IsNull(Datum),
          If(Previous([Ziekte Status]) = 'Ziek' and not IsNull(Previous(Datum)) and Previous(Naam) = Naam,
              Peek('OpeenvolgendeAantalDagenZiek') + 1,
              1),
          0) as OpeenvolgendeAantalDagenZiek,
     If([Ziekte Status] <> 'Ziek' and not IsNull(Datum),
          If(Previous([Ziekte Status]) <> 'Ziek' and not IsNull(Previous(Datum)) and Previous(Naam) = Naam,
              Peek('OpeenvolgendeAantalDagenNietZiek') + 1,
              1),
          0) as OpeenvolgendeAantalDagenNietZiek,
     If([Ziekte Status] = 'Ziek' and not IsNull(Datum),
          If(Previous([Ziekte Status]) = 'Ziek' and not IsNull(Previous(Datum)) and Previous(Naam) = Naam,
              If(Peek('OpeenvolgendeAantalDagenZiek') + 1 > Peek('MaxAantalDagenZiek'), Peek('OpeenvolgendeAantalDagenZiek') + 1, Peek('MaxAantalDagenZiek')),
              1),
          0) as MaxAantalDagenZiek,
     If([Ziekte Status] <> 'Ziek' and not IsNull(Datum),
          If(Previous([Ziekte Status]) <> 'Ziek' and not IsNull(Previous(Datum)) and Previous(Naam) = Naam,
              If(Peek('OpeenvolgendeAantalDagenNietZiek') + 1 > Peek('MaxAantalDagenNietZiek'), Peek('OpeenvolgendeAantalDagenNietZiek') + 1, Peek('MaxAantalDagenNietZiek')),
              1),
          0) as MaxAantalDagenNietZiek
          
Resident Expeditie
Order By Naam, Datum;



LOAD 
OpeenvolgendeAantalDagenNietZiek,OpeenvolgendeAantalDagenZiek,
     If(OpeenvolgendeAantalDagenZiek >= 0 and OpeenvolgendeAantalDagenZiek <= 7, '0-7',
        If(OpeenvolgendeAantalDagenZiek >= 8 and OpeenvolgendeAantalDagenZiek <= 42, '8-42',
            If(OpeenvolgendeAantalDagenZiek >= 43 and OpeenvolgendeAantalDagenZiek <= 365, '43-365',
                If(OpeenvolgendeAantalDagenZiek >= 366 and OpeenvolgendeAantalDagenZiek <= 1825, '366-1825', 'Other')
            )
        )
     ) as Verzuimduurklasse
          
Resident TEST
Order By Naam;

Thanks!

[Abrewmax]

Had this issue once while fixing our own leave script. Turned out I was merging everything into one list instead of pushing each period into its own entry. Once I stored each start/end pair separately, it finally showed up right. I even checked pedir ayudas at one point for ideas on sorting out messy records since their guides are super clear.

[marcus_sommer]

I think a resolving the periods to dedicated dates is mandatory - to be able to show the counts against periods by considering only certain days (working-days) as well as to prioritize all kinds of absence. Sickness may be overlapping with holidays, vacation, training, ... and this all together with the facts of sales/productives ... 

Beside such table the origin information should remain to count the frequency, to show details and on top may come some further aggregations, clustering, scoring and so on.