counting consecutive days for employee absences

[Quigwam2058]

I am working on a report that counts employee absences with a filter to alert for 3+ days absences.  I am attempting to count consecutive days absent using the Start Date in my attendance data which is sometimes entered daily and not as a date range  over multiple dates.

To do this I have the following expression as part of my solution to count consecutive dates by looking at the prior row using LAG FUNCTION 

IIF(StartDate - Lag(StartDate,1) = 1, 1, 0)

IIF(StartDate - Lag(StartDate,1) = 1, Lag(RowCount, 1) + 1, 1)

When initially creating these data items and check for errors I get "no errors".  But the report fails when I attempt to preview or run it.
Upon editing this data Item, I then have this error:

QE-DEF-0459 CCL Exception
"QE-DEF-0478  Invalid coercion from 'value' to 'member' for (Start Date)..."

1.  What causes this error?
2.  What better way can I come up with a count of consecutive dates from my data?

Thank you,
Cheryl

[bus_pass_man]

The error message is saying that you are trying to use a value rather than a member and the query engine doesn't like that.

This is because Lag is a dimensional function.  The first parameter it takes is a member.   

I would recommend, possibly, a function such as _days_between but it really depends on how the metadata is set up to record the absences.  You seem to indicate that the recording is inconsistent, which also might be a problem.

[Quigwam2058]

Yes there is a problem with the fact that i need to look at two different options - date ranges as well as multiple date entries.  so there I will need to compare the two calculations.

The error message is saying that you are trying to use a value rather than a member and the query engine doesn't like that.

This is because Lag is a dimensional function.  The first parameter it takes is a member.

I don't understand how I should change my expression to make it work- or are you saying it just won't work?

[bus_pass_man]

The query engine thinks that startDate is a column, not a member.  Because of this, it thinks that you are trying to coerce the values into members. 

If you are working with columns aka query items then you should work with relational functions, not dimensional functions. 

[Quigwam2058]

Yes, startdate is a column - I thought the lag function would return the value of the prior row in my query.  So, this expression cannot be used in my query because it consists of relational data?  Lag(StartDate, 1)

Another approach could be to create an index in my query - what would be the method for this because, rowcount doesn't seem to work either.

[dougp]

Cognos doesn't recognize LAG() as a relational function.  There are two ways to accomplish this in Cognos.

Fast:
Write SQL.

Slow:
use the row number function (running_count?) in your query.  Join it to itself on row = row - 1

[Quigwam2058]

Unfortunately since we are using cognos by a hosted service that we don't have access to, I cannot write sql.  And running_count is not being recognized when trying to create the expression.  what is the syntax for that function?

[Quigwam2058]

I found it  - I am using "running-count([Start Date]for [Employee Number] )"  this works to restart the row count on employee number change.  Now to write something that will group these and count them if they are sequential.

[Quigwam2058]

I added this additional calculated column, but  my report doesn't like it though it doesn't cause an error in the query.  Any insight into I can't use this to then group/count in my report:

dateadd ( {d}, - [running count], [Start Date])