Calculating a sum by Max date VA 8.5

[rjassem1]

This question is a little bit tricky. I'm trying to get the difference between the last head count for term one and the last head count for term two knowing that Head Count is a sum of individual registrations for that day. What this chart is doing is pulling daily enrollment. Daily enrollment increases by day and includes a sum of all individual registrations for that day.

The initial solution was to use Parallel Period and attempt to rank BUT we can't rank by most recent date because the max date is different between the two so Rank shows the most recent date that applies to both terms which is incorrect and if I change rank so it has the most recent date then the head count of the 2nd term would be excluded.

The other solution was to use an aggregate table BUT that would only work if we don't apply drop-down filters. If we add the drop-down filters then we end up in square one.  I tried using Max in a calculate item but I keep getting errors about a boolean value. 

How do I get the latest number for term one form the chart below - the latest number for term two while having the numbers change depending on the drop down selections? I feel that there must be a logical way to do this.

Any ideas are appreciated!

 

[Sam_SAS]

Hello,

Doing calculations with dates is really hard in VA and it is a priority for improvement. We have some features under development that should help, but I cannot promise what exactly or when.

There are some complicated workarounds (see Use the latest date in your VA report ) but I'm not sure if what you need to do will be possible using VA's expression syntax alone.

It might be easiest for you to precalculate the max date for each term as part of your source table. With that value stored, you can use ParallelPeriod() or possibly RelativePeriod() to get the difference between terms. You should just be able to apply a filter to the Key Value object to show the current head count.

Sam

[rjassem1]

I'm trying to think this through. The dates from the first year are fixed because it happened in the past but the dates for the 2nd year are updated daily so every day there is a new max date. 

[rjassem1]

For now, I might just do an overall aggregate and make a note that that this is an overall number and does not change using the drop-down filters.

[Sam_SAS]

In SAS Data Studio, I believe it should be possible to calculate the max date so that it updates every time the table is updated.

This is not something I have a lot of firsthand experience with, but it should possible if you have the permissions to do so.

Sam