Hello guys, So I want to create a graph with the following;
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.
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_)
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
... View more