Set analysis SUM of days on several months

[YD]

Hello everydody, bonjour,

I am trying to solve the following issue since couple days now, could somebody help me please?

I have to count the number of days per month of some employees

These employees got several contracts and some of them are accross two months

For example, if I select february 2019, I should have 28 days even if the contracy got:

Contract 1: StartDate on 20/01/2019 to EndDate on 25/02/2019

Contract 2: StartDate 25/02/2019 to EndDate 03/03/2019

The problem is when I selected both February and March, I have '0' and result

Here is my script, thank you in advance

//if the month of the start date contract is superior to the selected month

Sum(
if ( ((Num(Month([Contrats.DateFinContrat]))) > (Num(Month(Date#(GetFieldSelections([CalendrierBordereaux.Mois]), 'MMMM')))))
and ((Num(Month([Contrats.DateDebutContrat]))) = (Num(Month(Date#(GetFieldSelections([CalendrierBordereaux.Mois]), 'MMMM')))))
and [Contrats.IsContratPrevision]=0 and [Contrats.IsContratSup]=0
,

(1+floor(MakeDate(GetFieldSelections([CalendrierBordereaux.Annee]),Num(Month(Date#(GetFieldSelections([CalendrierBordereaux.Mois]), 'MMMM'))),Day(monthend(MakeDate(GetFieldSelections([CalendrierBordereaux.Annee]),Num(Month(Date#(GetFieldSelections([CalendrierBordereaux.Mois]), 'MMMM'))),'01')))))
-floor([Contrats.DateDebutContrat]))
, 0
)
)
+

//if the month of the contract is inferior to the selected month
Sum(
if ( ((Num(Month([Contrats.DateDebutContrat]))) < (Num(Month(Date#(GetFieldSelections([CalendrierBordereaux.Mois]), 'MMMM')))))
and ((Num(Month([Contrats.DateFinContrat]))) = (Num(Month(Date#(GetFieldSelections([CalendrierBordereaux.Mois]), 'MMMM')))))
and [Contrats.IsContratPrevision]=0 and [Contrats.IsContratSup]=0
,

(1+floor([Contrats.DateFinContrat])-
floor(MakeDate(GetFieldSelections([CalendrierBordereaux.Annee]),Num(Month(Date#(GetFieldSelections([CalendrierBordereaux.Mois]), 'MMMM'))),Day(monthstart(MakeDate(GetFieldSelections([CalendrierBordereaux.Annee]),Num(Month(Date#(GetFieldSelections([CalendrierBordereaux.Mois]), 'MMMM'))),'01'))))))
, 0
))

Thank you

[Stoyan_Terziev]

Hi YD,

May I kindly suggest you change a bit the concept.

Probably everywhere where you use the 'Month' function, you can change this to MonthName just to avoid Mar 2018 > Jan 2019.

Also, this part:
(Num(Month(Date#(GetFieldSelections([CalendrierBordereaux.Mois]), 'MMMM'))))

Will only work if one month is selected. Can you change it to: 

(Num(Month(Date#(Min({$} [CalendrierBordereaux.Mois]), 'MMMM'))))

Also, why don't you create your proper date on the back-end instead of doing this:

floor(MakeDate(
GetFieldSelections([CalendrierBordereaux.Annee])
,Num(Month(Date#(GetFieldSelections([CalendrierBordereaux.Mois]), 'MMMM')))
,Day(monthend(MakeDate(GetFieldSelections([CalendrierBordereaux.Annee]),Num(Month(Date#(GetFieldSelections([CalendrierBordereaux.Mois]), 'MMMM'))),'01')))))

If you must use this way, you can optimize it like so:

MonthEnd(MakeDate(Min({$}[CalendrierBordereaux.Annee]),Num(Month(Date#(Min({$}[CalendrierBordereaux.Mois]), 'MMMM'))),'01'))

There may be few things more that need to be done here before it becomes bulletproof but I hope that helps.

Kind regards,

S.T.