topic Re: How to calculate AVERAGE, MIN, MAX for an Aggregated Periodic Measure in SAS Visual Analytics

How to calculate AVERAGE, MIN, MAX for an Aggregated Periodic Measure

blewsas — Sun, 24 May 2020 16:00:52 GMT

Hi,

I've created the following aggregated periodic measure called "Consumed":

But when I try to calculate the average of that measure, I get a message that "Nested aggregated expressions are not supported":

So - how do I calculate the AVERAGE (and the MIN and the MAX)?

Thanks,

Barry Lewis

Re: How to calculate AVERAGE, MIN, MAX for an Aggregated Periodic Measure

PetriRoine — Mon, 25 May 2020 05:09:35 GMT

Hello @blewsas ,

The problem in your approach is that VA doesn't currently allow nested aggregated expressions i.e. aggregated operators inside aggregated operators.

VA 8.3 introduced capability to create an aggregated data source which allows us to create Consumed Avg, Consumed Min and Consumed Max without using nested aggregated operators. Although this approach has caveats like needing to define categories beforehand and possibly needing to Join the data to others for report actions to work.

Create aggregated data source

In the Data pane, select a data source.
Click Action icon

, and then select New data from aggregation of data-source. The New Aggregated Data window is displayed.
Select the data items Needed categories + Consumed, Time to add to the aggregated data source in the Available items list. Then, click +> to move it to the Selected items list.
(Optional) Change the name of the aggregated data source in the Name field.
Click Ok. The new aggregated data source is available in the Data pane.

Now your new data source has a measure Consumed of which aggregation you can define normally to be sum/avg/min/max etc...

Maybe this approach would work for you?

Best regards,

Petri

Re: How to calculate AVERAGE, MIN, MAX for an Aggregated Periodic Measure

blewsas — Mon, 25 May 2020 15:00:18 GMT

Thanks @PetriRoine,

Thanks for your reply. I thought about that approach too, but it doesn't seem to work. 😕

Here are my data items after I created the "Consumed" item using the calculation I previously posted.

However, when I create a new aggregated data source from this data source, "Consumed" is not one of the available items:

Re: How to calculate AVERAGE, MIN, MAX for an Aggregated Periodic Measure

PetriRoine — Wed, 27 May 2020 07:13:20 GMT

You are correct, that indeed seems to be the case. I did some testing and it appears that Aggregated Data Sources support f.ex. Aggregated (simple) and Aggregated (advanced) operators but not the Aggregated (periodic) operators.

Unfortunately I do not have another idea how to get the wanted end result.

Best regards,

Petri