cancel
Showing results for 
Search instead for 
Did you mean: 

Previous or RelativeValue for finding changes in monthly hours

Former Member
0 Kudos

Hello,

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:

=Sum(Count([SSN#)]
Where(
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.

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Come on, no replies?

I solved the issue by merging with a duplicate query, using a merge variable that lagged the week period.

But I still have problems with the relativevalue functions in a table that shows the development over time. On every year shift, it finds zero in the following period.


I am calculating the number of stopped users per 4-week-periode. To do this, I look at the number of active users (borgere) compared to active users in the following period, excluding new users in the following period (Tilgang). But for every year shift, the formula finds no following period. The table is sorted by year and 4-week-period.

Any ideas?

If I add [År] to the slicing_dims, I get a #COMPUTATION error!

Henrik

Former Member
0 Kudos

It is the formula for less hours that I have included 🙂

Former Member
0 Kudos

Oh - It is the formula for less hours I have included, of course. Still, same method. Any help?