topic Re: SAS VA - Line chart display 0 values in SAS Visual Analytics

SAS VA - Line chart display 0 values

MChan — Thu, 04 Apr 2019 14:30:03 GMT

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!

Re: SAS VA - Line chart display 0 values

PeterClemmensen — Thu, 04 Apr 2019 14:49:36 GMT

@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 🙂

Re: SAS VA - Line chart display 0 values

FalkoSchulz — Fri, 05 Apr 2019 08:14:00 GMT

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

Re: SAS VA - Line chart display 0 values

barry_van_dijk — Wed, 05 Jun 2019 07:09:27 GMT

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

Re: SAS VA - Line chart display 0 values

armte — Mon, 14 Dec 2020 22:22:29 GMT

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.

Re: SAS VA - Line chart display 0 values

MikeBsocal — Wed, 28 Sep 2022 18:16:48 GMT

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!

Re: SAS VA - Line chart display 0 values

MikeBsocal — Wed, 28 Sep 2022 18:57:43 GMT

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

Re: SAS VA - Line chart display 0 values

Sam_SAS — Wed, 28 Sep 2022 20:26:03 GMT

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?

Re: SAS VA - Line chart display 0 values

MikeBsocal — Wed, 28 Sep 2022 21:45:35 GMT

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:

Re: SAS VA - Line chart display 0 values

Sam_SAS — Thu, 29 Sep 2022 12:40:45 GMT

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.

Re: SAS VA - Line chart display 0 values

HunterT_SAS — Thu, 29 Sep 2022 13:06:17 GMT

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.

Re: SAS VA - Line chart display 0 values

MikeBsocal — Thu, 29 Sep 2022 16:57:37 GMT

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

Re: SAS VA - Line chart display 0 values

MikeBsocal — Thu, 29 Sep 2022 17:04:49 GMT

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

Re: SAS VA - Line chart display 0 values

Sam_SAS — Thu, 29 Sep 2022 17:41:25 GMT

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

Re: SAS VA - Line chart display 0 values

HunterT_SAS — Thu, 29 Sep 2022 18:39:16 GMT

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.

Re: SAS VA - Line chart display 0 values

MikeBsocal — Fri, 30 Sep 2022 18:10:24 GMT

@Sam_SAS @HunterT_SAS

I'm trying this: I've created a simple table (excel sheet) with 12 month numbers (1 through 12 for each month of year) and the corresponding month names in a second column.

Then I'm creating a calculated field in my main data that extracts the month from the Report Month -- so now I have a Month Number in both tables. I want to join these two tables on Month Number. Using a Left or Outer join. I'm hoping to force the 12 month numbers into the joined data, thus months with null patient count values will still be displayed.

I heard in a SAS Explore Training yesterday with Dominique Weatherspoon that I can only create a join using a Calculated Item with SAS Data Studio -- but it can't be done in VA. Do you know how to do that? I haven't figured it out yet. This is my first time in Data Studio... I can get to the join piece of it but I don't see the option to use the calculated field I created for Month Number.

Thanks