Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for
Did you mean:
Partner - Contributor II

## Year to date count

 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

Labels (2)

• ### Variables

1 Solution

Accepted Solutions
Creator II

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

4 Replies
Creator II

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;

Partner - Contributor II
Author

thank you @JMAROUF

Sorry but i didnt put the things correctly:

here is my datamodel:

History_Missing_Hours:
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

thank you

Creator II

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

Partner - Contributor II
Author

Thank you @JMAROUF

It works

Tags
Community Browser