BookmarkSubscribeRSS Feed
MChan
Calcite | Level 5

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!

 

SASVA.jpg

15 REPLIES 15
PeterClemmensen
Tourmaline | Level 20

@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
SAS Employee

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.

 

va74_line_chart_dates.png

 

Hope this helps. Falko

barry_van_dijk
Obsidian | Level 7

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

 

Line chart.png

armte
SAS Employee
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
Calcite | Level 5

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
Calcite | Level 5

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

 

 

MikeBsocal_0-1664391229328.png

 

Sam_SAS
SAS Employee

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
Calcite | Level 5

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:

MikeBsocal_0-1664401440982.png

 

Filtered down to one drug:

MikeBsocal_1-1664401471770.png

 

Sam_SAS
SAS Employee

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
SAS Employee

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
Calcite | Level 5

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

MikeBsocal
Calcite | Level 5

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
SAS Employee

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
SAS Employee

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:

HunterT_SAS_0-1664476647149.png

 

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. 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

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.

Discussion stats
  • 15 replies
  • 3228 views
  • 1 like
  • 8 in conversation