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

Showing results for

- Home
- /
- SAS Viya
- /
- Visual Analytics
- /
- SAS Visual Analytics New Aggregated Measure displays only number 1 ins...

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 12-18-2017 05:50 PM
(1041 views)

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.

6 REPLIES 6

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

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.