Year to date count

[LORENZ_GRN]

Month Name	Jan-2021	Feb-2021
	15/01/2021	31/01/2021	15/02/2021	28/02/2021
Person	YTD Missing Hours	YTD Missing Count	YTD Missing Hours	YTD Missing Count	YTD Missing Hours	YTD Missing Count	YTD Missing Hours	YTD Missing Count
Mark Smith	1	1	5	2	7	3	4	4
Joe White	0	0	1	1	0	1	3	2

Hello, I need to calculate in table the variable "YTD Missing Count" which I exposed in the table above, it's the cumulative count of times you have the variable "YTD Missing Hours" >0 for each fortnight.

Could you please explain the formula in order to calculate "YTD Missing Count"?

Thank you very much indeed

[JMAROUF]

Hello  @LORENZ_GRN ,

I hope it will help:

DATA:

Missing_Hours:
Load monthname(date(Dates,'DD/MM/YYYY'),0) as MonthYear,* inline [
Dates,Person,MissionH
15/01/2021,'Mark Smith',1
31/01/2021,'Mark Smith',
15/02/2021,'Mark Smith',5
28/02/2021,'Mark Smith',
15/03/2021,'Mark Smith',7
31/03/2021,'Mark Smith',
15/04/2021,'Mark Smith',4
30/04/2021,'Mark Smith',
15/01/2021,'Joe White',0
31/01/2021,'Joe White',
15/02/2021,'Joe White',1
28/02/2021,'Joe White',
15/03/2021,'Joe White',0
31/03/2021,'Joe White',
15/04/2021,'Joe White',3
30/04/2021,'Joe White',
];

Expression:

MH: Sum(MissionH)

CMH: = rangesum(before(total count( {<Dates=,[MissionH]={">0"}>}1),0,NoOfColumns(TOTAL)))

Output:

Regards;

[LORENZ_GRN]

thank you @JMAROUF 

Sorry but i didnt put the things correctly:

here is my datamodel:

History_Missing_Hours:
Load monthname(date(Real_Time_Date,'DD/MM/YYYY'),0) as RealTime_MonthYear,
Person, date(Real_Time_Date,'DD/MM/YYYY') as Real_Time_Date,
date(History_Date,'DD/MM/YYYY') as History_Date,History_Missing_Hours
inline [
Person,Real_Time_Date,History_Date,History_Missing_Hours
Mark Smith,44331,44326,8
Mark Smith,44331,44327,8
Mark Smith,44316,44288,8
Mark Smith,44316,44316,6
Mark Smith,44316,44211,1
Mark Smith,44301,44288,8
Mark Smith,44301,44292,8
Mark Smith,44301,44211,1
Mark Smith,44286,44211,1
Mark Smith,44270,44211,4
Mark Smith,44255,44211,0
Mark Smith,44242,44211,4
Mark Smith,44227,44211,1
Mark Smith,44211,44211,0
Joe White,44331,44326,8
Joe White,44331,44325,4
Joe White,44316,44270,2
Joe White,44316,44265,1
Joe White,44301,44241,4
Joe White,44301,44227,3
Joe White,44286,44271,0
Joe White,44270,44237,2
Joe White,44255,44239,4
Joe White,44255,44237,4
Joe White,44242,44235,0
Joe White,44227,44226,0
Joe White,44227,44212,0
Joe White,44211,44206,5
Joe White,44211,44205,1
];

this is the output:

CMH formula doesnt work for my datamodel:

rangesum(before(total count( {<Real_Time_Date=,[MH]={">0"}>}1),0,NoOfColumns(TOTAL)))

it should count the year to date times MH>0

could you please help me out?

thank you

[JMAROUF · Accepted solution]

Hello @LORENZ_GRN ,

Try this:

MH: Sum(History_Missing_Hours)

CMH:   rangesum( before(total count(distinct {<History_Date=,[History_Missing_Hours]={">0"}>}1),0,NoOfColumns(TOTAL)),0)

Regards;

JM

[LORENZ_GRN]

Thank you @JMAROUF 

It works