turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Visual Analytics
- /
- SAS Visual Analytics New Aggregated Measure displa...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-18-2017 05:50 PM

I am trying to create a new, derived field to use in tables and graphs. I have followed the steps outline in many pages/tutorials but the field is not calculating as expected. I want to create a line chart noting the volume (frequency) and mean by month. (adding UCL and LCL fields as well but am having same issue with those fields, presumably because they use the mean in their calculations) Screenshots attached. I don't understand why the mean is not calculating but only displaying a 1.00 per month (category). I attempted to create a new calculated field but the frequency measure does not appear and I have no other field that is numeric. I see how to add a reference line, but I don't want to have to recalculate it each time. I am using SAS VA 7.3. Thanks in advance for your assistance.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jlr337

12-20-2017 02:40 AM

This is how I see it...

Since frequency is 1 on every row in the data the average can never be anything else than 1, regardless how you group the data.

//Fredrik

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to FredrikE

12-20-2017 10:24 AM

Thank you for the response FriderikE. That's what I was afraid of. Can you recommend how I could get it to work? I need a mean value of the total volume by month. When I calculate the mean, UCL, LCL, and Stdev in Excel, it looks like this (see attached). As new data is entered, monthly rows would be added and these values would update accordingly, ideally, without having to add/update anything manually. I am attempting to create a chart in VA with these data, then pulling into Excel using SAS add-in so end user can update as needed just by refreshing. My back up plan is to create tab in excel to calculate and create the charts, but I was hoping to do this in VA so that it can be as 'automated' as possible.

(if you see an issue with my math, please let me know. I know there is a way to calculate the mean/ucl/lcl that adjusts but I don't know how to do that, so I'm keeping it simple- but it still may not be correct)

Thank you for the response FriderikE. That's what I was afraid of. Can you recommend how I could get it to work? I need a mean value of the total volume by month. When I calculate the mean, UCL, LCL, and Stdev in Excel, it looks like this (below). As new data is entered, monthly rows would be added and these values would update accordingly, ideally, without having to add/update anything manually. I am attempting to create a chart in VA with these data, then pulling into Excel using SAS add-in so end user can update as needed just by refreshing. My back up plan is to create tab in excel to calculate and create the charts, but I was hoping to do this in VA so that it can be as 'automated' as possible. (if you see an issue with my math, please let me know. I know there is a way to calculate the mean/ucl/lcl that adjusts but I don't know how to do that, so I'm keeping it simple- but it still may not be correct) mean=avg($B$1:$B$17), stdev=stdev.p($B$1:$B$17), ucl=C1+(3*D1), lcl=C1-(3*D1) mon(A) total(B) mean(C) stdev(D) ucl(E) lcl(F)

Thanks for your help.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jlr337

12-20-2017 12:20 PM

I have tried some ideas but I don't manage to get the total sum of periods. The total of frequency works fine.

Sorry, but I think the problem is that it is not possible to do aggregations on aggregations....

//Fredrik

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to FredrikE

12-20-2017 12:24 PM

Gotcha. That's too bad, but I really appreciate your help with this. Thanks! Jenefer

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jlr337

12-21-2017 07:32 AM

Can't let this go yet.....

Maybe this can work if you look at one year at the time....:

First create a aggregated variable with the total sum of frequency:

Then create a second aggregated variable that counts the distinct periods:

Finally create a aggregated variable that do the division var1 / var2.

I think this might work ?

//Fredrik

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to FredrikE

12-21-2017 09:41 AM

Thanks for sticking with me :-) Your suggestion makes sense logically, but I'm not sure I did it correctly because I'm still seeing just 1.00. However, I found another suggestion for how to get around this which is to add the mean variable to the sas dataset from which the VA chart is build. I am going to try that next. Thanks again, Jenefer.