BookmarkSubscribeRSS Feed
PhiSlogan
Obsidian | Level 7

Hello guys, So I want to create a graph with the following; 

 

  1. The x-axis will be a date hierarchy, the y-axis value on the graph must be the value of the last day recorded of that year- using my data table for 2016 i should display value of 31 DEC 2016 and for 2017 I should show value of 28 FEB 2017. 
  2. When I click the x-axis in 2016 it will show me all the recorded months for 2016 on the x-axis and their values. But the values have to be the last day of the month.

Can anyone help me achieve this with with SAS VA 7.5 Report Builder ?

I have done the following:
- created Hierarchy item containing other items; Year, Trimester, Month, Day.  <-- this is my x-axis

- created 2 calculated measures total_1 and total_2 

  - total_1 calculation:  if (category=1) return total else 0  

  - total_2 calculation:  if (category=2) return total else 0  

- created 2 calculated aggregated measures total_1_agg and total_2_agg <--- y-axis

  - total_1_agg calculation:  Last [_ByGroup_] ('total_1'n, 'data'n,_IncludeMissing_)

  - total_2_agg calculation:  Last [_ByGroup_] ('total_2'n, 'data'n,_IncludeMissing_)

 

PhiSlogan_0-1610353781489.png
Date Total Amount Category
29-Nov-16 8 1
29-Nov-16 9 2
30-Nov-16 7 1
30-Nov-16 9 2
30-Dec-16 7 1
30-Dec-16 5 2
31-Dec-16 3 1
31-Dec-16 8 2
30-Jan-17 1 1
30-Jan-17 5 2
31-Jan-17 3 1
31-Jan-17 10 2
27-Feb-17 1 1
27-Feb-17 3 2
28-Feb-17 3 1
28-Feb-17 6 2

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 0 replies
  • 744 views
  • 0 likes
  • 1 in conversation