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

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

jlr337 — Mon, 18 Dec 2017 22:50:09 GMT

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.

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

FredrikE — Wed, 20 Dec 2017 07:40:32 GMT

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

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

jlr337 — Wed, 20 Dec 2017 15:24:28 GMT

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

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

FredrikE — Wed, 20 Dec 2017 17:20:41 GMT

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

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

jlr337 — Wed, 20 Dec 2017 17:24:33 GMT

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

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

FredrikE — Thu, 21 Dec 2017 12:32:42 GMT

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

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

jlr337 — Thu, 21 Dec 2017 14:41:59 GMT

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.