Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
LORENZ_GRN
Partner - Contributor II
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)
1 Solution

Accepted Solutions
JMAROUF
Creator II
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

View solution in original post

4 Replies
JMAROUF
Creator II
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:

JMAROUF_0-1621347147166.png

 

Regards;

LORENZ_GRN
Partner - Contributor II
Partner - Contributor II
Author

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:

LORENZ_GRN_0-1621523148877.png

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
Creator II
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

LORENZ_GRN
Partner - Contributor II
Partner - Contributor II
Author

Thank you @JMAROUF 

It works