BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
AC34
Calcite | Level 5

Hi all

My task is going to build a bar chart with a a straight average line across the bars, could you send some lights on creating the average distinct count variable please? Currently the line is a trend line but I need a straight line cross the bars. Like the red line in below chart.

 

Variables:

Event ID - Distinct count (Event ID set as distinct count)

Event assigned QID upper northland (staff ID)

 

The formula and expected(in red line)/current(orange line) chart as below:

 

formula like sum the distinct count then divide by the distinct count staff id

 

Sum [_ForAll_] (AggregateTable(_Sum_, Table(_Avg_, Fixed(
'EventNum-string'n, 'Event Assigned QIDs - upper northland'n),
'Frequency'n))) / Distinct [_ByGroup_] (
'Event Assigned QIDs - upper northland'n)

AC34_0-1664879564080.png

 

AC34_1-1664871172655.png

 

 

 

Thanks

AC

1 ACCEPTED SOLUTION

Accepted Solutions
acordes
Rhodochrosite | Level 12

It's tricky.

 

I would consider a two-steps approach with an aggregated table first. Then you can create an aggregated item on a fixed quantity preventing the error message you received.

 

Nevertheless you can achieve it, but it's so tricky that I recommend my earlier suggestion. 

 

Here's the tricky solution:

The grand average distinct count in my opinion needs a higher aggregation to make it work. in my case this is year as the graph's x axis is month. 

 

AggregateTable(_Sum_, Table(_Sum_, Fixed('_year'n), Table(
_CountDistinct_, Fixed('TMAIMG_MODELINFO'n, 'id'n), 1))) / 12

pic3.pngpic2.pngpic1.png

pic5.pngpic4.png

 

View solution in original post

1 REPLY 1
acordes
Rhodochrosite | Level 12

It's tricky.

 

I would consider a two-steps approach with an aggregated table first. Then you can create an aggregated item on a fixed quantity preventing the error message you received.

 

Nevertheless you can achieve it, but it's so tricky that I recommend my earlier suggestion. 

 

Here's the tricky solution:

The grand average distinct count in my opinion needs a higher aggregation to make it work. in my case this is year as the graph's x axis is month. 

 

AggregateTable(_Sum_, Table(_Sum_, Fixed('_year'n), Table(
_CountDistinct_, Fixed('TMAIMG_MODELINFO'n, 'id'n), 1))) / 12

pic3.pngpic2.pngpic1.png

pic5.pngpic4.png

 

sas-innovate-2024.png

📢

ANNOUNCEMENT

The early bird rate has been extended! Register by March 18 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

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.

Discussion stats
  • 1 reply
  • 724 views
  • 1 like
  • 2 in conversation