Exploring, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

SAS Visual Analytics New Aggregated Measure displays only number 1 instead of Avg

Reply
Occasional Contributor
Posts: 6

SAS Visual Analytics New Aggregated Measure displays only number 1 instead of Avg

 

 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.

 

 

PROC Star
Posts: 399

Re: SAS Visual Analytics New Aggregated Measure displays only number 1 instead of Avg

This is how I see it...Smiley Happy

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

Occasional Contributor
Posts: 6

Re: SAS Visual Analytics New Aggregated Measure displays only number 1 instead of Avg

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.

PROC Star
Posts: 399

Re: SAS Visual Analytics New Aggregated Measure displays only number 1 instead of Avg

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

Occasional Contributor
Posts: 6

Re: SAS Visual Analytics New Aggregated Measure displays only number 1 instead of Avg

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

PROC Star
Posts: 399

Re: SAS Visual Analytics New Aggregated Measure displays only number 1 instead of Avg

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

 

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

 

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

avg_total_1.png

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

avg_total_2.png

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

 

I think this might work ?

//Fredrik

Occasional Contributor
Posts: 6

Re: SAS Visual Analytics New Aggregated Measure displays only number 1 instead of Avg

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.

Ask a Question
Discussion stats
  • 6 replies
  • 306 views
  • 2 likes
  • 2 in conversation