Forum Discussion
Calculate sick report frequency
Hi all, I need to show how many times an employee called in sick during a time period. I have a table of time registration data to do this. Below you will find a simplified example of this table. ...
Anonymouscan you please provide a sample data to work with?
To be clear, you mentioned "that employee 1201 called in sick on June 11 2018 for the first time (HourCode 160 = sick). On June 20 2018 he/she reported sick again. " But 12,13,21 and 22 June 1201 has corresponding 160 too. What does that signify? Does it not mean he called in sick on those days too ?
If I understand it correctly, do you want to see for how many blocks of consecutive days someone called in sick ?
Hi smpa01,
You're right, I'm trying to count how many blocks of consecutive days 1201 called in sick (2 blocks). So I'm not trying to count then number of sick days themselves (6 days).
Rg. Erwin
Does this work for you?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBNCkAhCEbRvTgO0q+f11tLtP9thEQQKkSDM9CLc1LPkmVQIuGiP1hoJfV6XN+BZqFb+CwMC3K3dX4QEZYI6xt70YWJ3/tbAQfzEeUhykOLStxF4E4C14a3bW0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DT = _t, HourCode = _t, EMP = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"DT", type date}, {"HourCode", Int64.Type}, {"EMP", Int64.Type}}), #"Filled Down" = Table.FillDown(#"Changed Type",{"HourCode", "EMP"}), #"Added Index2" = Table.AddIndexColumn(#"Filled Down", "Row#", 1, 1), #"Added Custom" = Table.AddColumn(#"Added Index2", "Custom", each if [HourCode]=160 then 1 else 0), #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"Added Index1",JoinKind.LeftOuter), #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Custom"}, {"Custom.1"}), #"Added Custom1" = Table.AddColumn(#"Expanded Added Index1", "Custom.2", each Text.From([Custom.1])&Text.From([Custom])), #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.2] = "01")), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom.1", "Custom.2"}), #"Added Index3" = Table.AddIndexColumn(#"Removed Columns", "Index.2", 1, 1), #"Added Index4" = Table.AddIndexColumn(#"Added Index3", "Index.3", 0, 1), #"Merged Queries1" = Table.NestedJoin(#"Added Index4",{"Index.2"},#"Added Index4",{"Index.3"},"Added Index4",JoinKind.LeftOuter), #"Expanded Added Index4" = Table.ExpandTableColumn(#"Merged Queries1", "Added Index4", {"Index"}, {"Index.4"}), #"Added Custom2" = Table.AddColumn(#"Expanded Added Index4", "Custom.1", each if [Index.4]<> null then [Index.4]-1 else Table.RowCount(#"Filled Down")), #"Added Index5" = Table.AddIndexColumn(#"Added Custom2", "Index.5", 1, 1), #"Added Custom3" = Table.AddColumn(#"Added Index5", "Custom.2", each {[Index]+1..[Custom.1]}), #"Expanded Custom.2" = Table.ExpandListColumn(#"Added Custom3", "Custom.2"), #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom.2",{"Index.5", "Custom.2"}), Custom1 = let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBNCkAhCEbRvTgO0q+f11tLtP9thEQQKkSDM9CLc1LPkmVQIuGiP1hoJfV6XN+BZqFb+CwMC3K3dX4QEZYI6xt70YWJ3/tbAQfzEeUhykOLStxF4E4C14a3bW0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DT = _t, HourCode = _t, EMP = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"DT", type date}, {"HourCode", Int64.Type}, {"EMP", Int64.Type}}), #"Filled Down" = Table.FillDown(#"Changed Type",{"HourCode", "EMP"}), #"Added Index2" = Table.AddIndexColumn(#"Filled Down", "Row#", 1, 1), #"Added Custom" = Table.AddColumn(#"Added Index2", "Custom", each if [HourCode]=160 then 1 else 0), #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"Added Index1",JoinKind.LeftOuter), #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Custom"}, {"Custom.1"}), #"Added Custom1" = Table.AddColumn(#"Expanded Added Index1", "Custom.2", each Text.From([Custom.1])&Text.From([Custom])), #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.2] = "01")), #"Added Custom2" = Table.AddColumn(#"Filtered Rows", "FRQ", each 1), #"Merged Queries1" = Table.NestedJoin(#"Added Custom1",{"Row#"},#"Added Custom2",{"Row#"},"Filtered Rows",JoinKind.LeftOuter), #"Expanded Filtered Rows" = Table.ExpandTableColumn(#"Merged Queries1", "Filtered Rows", {"FRQ"}, {"FRQ"}) in #"Expanded Filtered Rows", #"Merged Queries2" = Table.NestedJoin(#"Removed Other Columns",{"Custom.2"},Custom1,{"Row#"},"Table1",JoinKind.LeftOuter), #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries2", "Table1", {"DT", "HourCode", "EMP", "Row#", "Custom", "Index", "Index.1", "Custom.1", "Custom.2", "FRQ"}, {"DT", "HourCode", "EMP", "Row#", "Custom", "Index", "Index.1", "Custom.1", "Custom.2.1", "FRQ"}), #"Filtered Rows1" = Table.SelectRows(#"Expanded Table1", each ([HourCode] = 160)), #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows1",null,1,Replacer.ReplaceValue,{"FRQ"}), #"Merged Queries3" = Table.NestedJoin(Custom1,{"Index"},#"Replaced Value",{"Index"},"Custom1",JoinKind.LeftOuter), #"Expanded Custom1" = Table.ExpandTableColumn(#"Merged Queries3", "Custom1", {"FRQ"}, {"FRQ.1"}), #"Added Custom4" = Table.AddColumn(#"Expanded Custom1", "Custom.3", each if [FRQ]=null and [FRQ.1]=null then null else if [FRQ]<>null and [FRQ.1]=null then [FRQ] else [FRQ.1]), #"Renamed Columns" = Table.RenameColumns(#"Added Custom4",{{"FRQ", "FRQ_Start"}}), #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Custom", "Index", "Index.1", "Custom.1", "Custom.2", "FRQ.1"}), #"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"Row#", Order.Ascending}}), #"Renamed Columns1" = Table.RenameColumns(#"Sorted Rows",{{"Custom.3", "FRQ_Consecution"}}) in #"Renamed Columns1"