I have 1.5M transactions data with a date field "accounting date" from 1/1/2017 to 3/31/2019. In SAS VA, I duplicated the category accounting date to format in 'Month' to show a comparison of 2017, 2018, and current 2019 spend. However, even though there is no data from April to December 2019, The 2019 line is showing $0 value from April to December. I would prefer it to stop at March 2019. Any advise would help. Thanks!
The default behavior is in fact to stop rendering data points if there is no data. Are you sure - that your data source indeed doesn't contain any data rows for the given dates? It feels like there are rows holding the value 0? It either needs to be a missing value or entirely missing as row.
I had a quick try using some sample data here using VA 7.4 and the line for 2012 stops as expected in July given that's the last data available in my data set.
Hope this helps. Falko
I have a VA 7.4 example related to this question. Please see attached line chart. This example is the result of several filters applied to the data. As you can see there is no value for the months 3, 5, 6 and 7 of 2018 and months 5 and 6 of 2019.
I understand that I would need to modify my dataset in such a way that 0 is the result for these months instead of a null (.) value. How can I best accomplish this when the measure shown is the number of records resulting from multiple filters that can be applied to the dataset?
Hi @armte did you ever figure out how to solve this? I too have a situation where I want my line chart to drop to 0 from months where there is null data. However the line chart just kind of skips over those null dates with a straight line which makes it appear that there is data for those null months.
To add to the previous comment, here's an example of how VA visualizes for months with null data. We're trying to have zero displayed on the line chart when a month has null data; and on a bar chart (lower chart in pic) we're trying to have months display where the value is null.
@Sam_SAS is this something you might know how to resolve? Thanks
We're getting closer. Using your formula we can generate a zero for a null when some other measure forces every month to display. However when we filter it down to our drug of interest -- if it doesn't have a value in every month -- all the null-now-zero values and months disappear.
With all drugs:
Filtered down to one drug:
Oh, I see -- some months, some of the drugs don't have a row in the table. I don't think you can solve this using the VA expression editor.
It would probably be simplest to make some sort of loop expression in your data preparation stage to generate the missing values or zeroes. I think? I think it should be possible, but I am not an expert by any means at data prep.
Echoing what @Sam_SAS said, what you are wanting to do is not currently possible. Visual Analytics cannot make up data points that do not exist in the data. The only exception is crosstabs where we have to make up data crossings that may not exist in the data.
There is an open feature request for the line chart scenario and I have added this thread to it. The only way around it for the time being will be to add rows for the non-existent months into your data.
Thanks Sam. Is there anything you can think of as a workaround? Such as grabbing a measure that is always populated for every month then using it as a Hidden value or something like that? I've been experimenting with that but haven't yet landed on anything that works.
How are your other clients doing this? It's a common chart to show: a years worth of values wherein some of the months have null values...
I think the problem you're going to find here is once you filter out these categorical values for Current_Product that have the Months you're looking for, then it won't matter what you do unfortunately with Hidden measures or anything else.
This does remind me though, another way to get the same type of crossing for other objects is to use the Lattice roles. I made up some data similar to what you're doing, so here in a bar chart with Lattice Row = Current_Product:
Between the two values for "Current_Product", all months will be displayed and you'll see no bars for months where there is no data. But again once you get rid of one of these Products, the months for those will go away too.
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.