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!
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.
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.