SAS VA - Line chart display 0 values

[MChan]

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!

[PeterClemmensen]

@MChan, I moved your post to the VA Community. I think you'll have more luck getting a good answer here.

Also, welcome to the SAS Communities 🙂

[FalkoSchulz]

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

[barry_van_dijk]

Dear 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?

Kind regards,

Barry

[armte]

Hey @barry_van_dijk, did you figure out a solution to your problem? I'm having the same issue where there are rows for certain dates but some dates don't have any entries. When I plot this, it shows the change from one date to another and skips over the dates in between where I would like to have the line drop to 0 and then back up to the next date that has rows of data. The y-axis in my situation is a distinct function with a group-by context.

[MikeBsocal]

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.

Thanks!

[MikeBsocal]

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

[Sam_SAS]

You should be able to create a calculated measure with an expression like,

IF 'Discontinued Patients'n NotMissing

RETURN 'Discontinued Patients'n

ELSE 0

I think this should work?

[MikeBsocal]

Thanks @Sam_SAS 

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:

[Sam_SAS]

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.

[HunterT_SAS]

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. 

[MikeBsocal]

Thanks @HunterT_SAS . Just saw this response now. After I'd already replied to Sam.

[MikeBsocal]

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...

Thanks

Mike

[Sam_SAS]

Hi Mike,

Your idea of using a measure in the Hidden data role is interesting and might be worth a shot. I would not be shocked if you could trick VA into doing what you want, but it is not normally supposed to work this way.

Sam

[HunterT_SAS]

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.