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 |
|