Skip to Content
Jul 31, 2018 at 11:02 AM

Previous or RelativeValue for finding changes in monthly hours



I am trying to make a table, that shows development in home care hours.

For each 4-week-period, I want the number of citizens with

  • unchanged aid,
  • more aid
  • less aid
  • new citizens (Start)
  • citizens that no longer receives aid (Stop).

So far, I have made variables for each of these, and I do get results.

However, the results are incorrect. If a Citizen received aid a year ago, but not in the last four-week period, my variable will identify last year as the previous period, and therefore simply skip the interim periods. This is not what I want - if a Citizen stops receiving home care hours, this should be registered as a Stop, and if the Citizen receives aid a year later, this is a start.

My variable code looks like this for the counter for citizens with more aid than in the last 4-week-periode:

Previous([hours];([SSN#])) In([SSN#)];[Year];[4-week])
> (Sum([hours] In([[SSN#];[Year];[4-week])))

I am having trouble understanding the interaction of the previous function and the context operators. I have tried using relativevalue, but it seems that this is a more restricted function that will only work in a sorted table - despite documentation stating the opposite.