BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Carrot17
Quartz | Level 8

Hi,

 

I would like to seek your help regarding our issue in SAS VA Butterfly chart. Our requirement is to display the Headcount by Age Group (>=60, 55-59, 50-54 up to <=24) for the Previous Year and Current Year. The left side of the butterfly is the Previous Year and the right side is the Current Year and the data and legend is dynamic based on the selected drop-down list control Period 1 (Previous) and Period 2 (Current) plus there are 6 controls like Org Level, Personnel Group etc. I already created the butterfly chart. Yes, the Period 1 and Period 2 legend is dynamic the butterfly chart displays the correct data. However, if we select specific Org Level and that Org level let say has no data really not available in the datasource for Age Group >=60 or <=24 for Previous Year but has a value for Current Year the sorting of Age Group (Y axis) will not be sorted properly even there's a custom sort. Also we created a diffefent version of butterfly chart but there are some requirement that will/might be compromise. Please help. Thank you so much.

 

 

Regards,

Carrot17

 

1 ACCEPTED SOLUTION

Accepted Solutions
Carrot17
Quartz | Level 8
Thanks Anna. We already figure out the root cause for sort issue of category, switching color etc. it is due to the missing values or no corresponding age band for specific filter. The solution is we created a lookup table and left join them to the raw data query to get or show the age band even they were not available or null from raw data query. however, we encountering long run when we ran the DQ as the lookup table consists of huge amount of data even they where unique rows (36 billion+ rows).


Regards,
Carrot17

View solution in original post

10 REPLIES 10
Sam_SAS
SAS Employee
Hi Carrot,

A screenshot would be very helpful for us to understand what you want to do. Your scenario is rather complicated.

Thanks,
Sam
Carrot17
Quartz | Level 8

Hi,

 

Before screenshot is the chart that I created, by default, the categories will be sorted and the colours are displayed light for Jan 2010 and dark for Jan 2015.

 

After screenshot, I had tried to create several charts however the issue is not resolved.

The requirements :

Category : To be sort as descending

Bar Color: Left side should always Light color, Right side should always dark color

Period: Period 1 (Previous Year) always on the Left side, Period 2 (Current Year) always on the Right side

Chart 1: Custom Chart

Category is shared role

Bar Chart 1 Measure

Bar Chart 2 Measure

Bar Group 1

Bar Group 2

Bar 1 Fill color – is set to Data Color 1

Bar 2 Fill color – is set to Data Color 2

X-Axis: Under Missing Data&colon; the “Show missing groups” is not selected both Bar 1 and Bar 2

Note:

Category: not sorted.

Bar Color: display correctly. Doesn’t switch (which is correct)

Period: Display properly

 

Chart 2: Custom Chart

Category is shared role

Bar Chart 1 Measure

Bar Chart 2 Measure

Bar Group 1

Bar Group 2

Bar 1 Fill color – is set to Data Color 1

Bar 2 Fill color – is set to Data Color 2

Under Missing Data&colon; the “Show missing groups” is selected both Bar 1 and Bar 2

X-Axis: Under Axis Label: “Show axis label” is selected both Bar 1 and Bar 2

Legend is set to Visible Off

Note:

Category: sorted properly

Bar Color: display correctly. Doesn’t switch (which is correct)

Period: Static Period

Having static period, we will not able to know what is the period values unless we mouse over the bar

 

Chart 3: Custom Chart

Role:

Category is shared role

Bar Chart 1 Measure

Bar Chart 2 Measure

Bar Group 1

Bar Group 2

Property:

Bar 1 Fill color – is set to Data Color 1

Bar 2 Fill color – is set to Data Color 2

Under Missing Data&colon; the “Show missing groups” is selected both Bar 1 and Bar 2

X-Axis: Under Axis Label: “Show axis label” is not selected both Bar 1 and Bar 2

Legend is set to Visible Off

Note:

Category: sorted properly

Bar Color: display correctly. Doesn’t switch (which is correct)

Period: Not visible

Issue same as Chart 2, we will not know what is the period if we hide the period value

 

Chart 4: Custom Chart

Role:

Category is shared role

Bar Chart 1 Measure

Bar Chart 2 Measure

Group is shared role

Property:

Bar 1 Fill color – is set Automatic default

Bar 2 Fill color – is set Automatic default

Under Missing Data&colon; the “Show missing groups” is selected both Bar 1 and Bar 2

X-Axis: Under Axis Label: “Show axis label” is not selected both Bar 1 and Bar 2

Legend is set to Visible

Display Legend: Bar Chart 1 only.

Note:

Category: sorted properly

Bar Color: came to same color both Bar 1 and Bar 2 (if age is not available in the data source based on the selected Group)

Period: display correctly

 

Chart 5: Custom Category

Role:

Category is shared role

Bar Chart 1 Measure

Bar Chart 2 Measure

Group is shared role

Property:

Bar 1 Fill color – is set Automatic default

Bar 2 Fill color – is set Data Color 2

Under Missing Data&colon; the “Show missing groups” is selected both Bar 1 and Bar 2

X-Axis: Under Axis Label: “Show axis label” is not selected both Bar 1 and Bar 2

Legend is set to Visible

Display Legend: Bar Chart 1 only.

Note:

Category: sorted properly

Bar Color: color switch (if age is not available in the data source based on the selected Group)

Period: display correctly

 

I had tried several ways however it is still not able to meet the requirements. I realised that issue for chart 1, 4 & 5 were existed if the selected group have missing data - no corresponding age range in the data source.

Please advise if there is any other solution to overcome this.

 

Thank you!

 

Sam_SAS
SAS Employee

If you don't mind, I will post your images inline for the convenience of other readers:

 

Before:

 

Before.png

 

After:

 

After.png

Sam_SAS
SAS Employee

If I understand correctly, Chart 2 is the closest to what you want but it is using a static period and you want the period(s) to be selectable through prompts. Is that right?

Carrot17
Quartz | Level 8
Yes. Your understanding is correct. The Period should be dynamic based on the selected Period controls. The "Period 1" and "Period 2" are just a label name of Measures that's why they were static. We did the dynamic Period but it may compromise the sorting of category even custom sort assigned.

To me the closest is Chart 1 if Age is not missing. The only problem in Chart 1 is the sort issue of category when data is really not exist in the datasource.

Thanks for the reply. Appreciated. ☺
Carrot17
Quartz | Level 8
Hello Guys!

Any update on this? 😃 Thank you.


Regards,
Carrot17
AnnaBrown
Community Manager

Hi Carrot17,

 

This scenario is best directed to Technical Support. Feel free to open a track. For information on other ways to contact Tech Support, refer to: http://support.sas.com/techsup/contact/index.html.

 

Best,

Anna


Join us for SAS Community Trivia
SAS Bowl XXIX, The SAS Hackathon
Wednesday, March 8, 2023, at 10 AM ET | #SASBowl

Carrot17
Quartz | Level 8
Thanks Anna. We already figure out the root cause for sort issue of category, switching color etc. it is due to the missing values or no corresponding age band for specific filter. The solution is we created a lookup table and left join them to the raw data query to get or show the age band even they were not available or null from raw data query. however, we encountering long run when we ran the DQ as the lookup table consists of huge amount of data even they where unique rows (36 billion+ rows).


Regards,
Carrot17
Carrot17
Quartz | Level 8

Hi,

Above solution is technically correct. However, we encountered long run due to our lookup DQs generate 36billion+ of raw data. Any suggestion to reduce the data? Thanks in advance.

In addition, below is the implementation we did.
We created a 9 lookup dq(s) to get the unique data please note that the data source of lookup dq(s) are from transaction table. After we created the 9 dq(s), we combined the 9 dq(s) to get the unique possible combination which generate 36billion of raw data. Please help. Thank you.


Best Regards,
Carrot17

AnnaBrown
Community Manager

Thanks for the update, Carrot17, I checked around with some SAS experts here and think it'd still be best to contact Tech Support with this one. http://support.sas.com/techsup/contact/index.html.

 

Anna


Join us for SAS Community Trivia
SAS Bowl XXIX, The SAS Hackathon
Wednesday, March 8, 2023, at 10 AM ET | #SASBowl

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 10 replies
  • 2937 views
  • 1 like
  • 3 in conversation