BookmarkSubscribeRSS Feed
Suvi2020
Fluorite | Level 6

Hi VA Users,

 

I want to create a cross tab report which will show the End of period values rather than the summing of the values. 

 

The data looks like this 

Category_lvl_1Category_lvl_2LOBValueEnd_of_Month
Cat1Sub_cat1LOB12131-Jan-20
Cat1Sub_cat3LOB11931-Jan-20
Cat1Sub_cat2LOB23129-Feb-20
cat2Sub_cat3LOB12431-Jan-20
cat2Sub_cat4LOB25429-Feb-20

 

The page filter will be END_OF_Moth

So the measure column "Value" Should show the values based on the page filter. Like, if the page is not filtered then it should show 29-Feb-20 values. If let's say the page is filtered on "31-Jan-20" then for cat 1 it should show for Cat1/LOB1 cross tab (21+19) = 40. 

 

Is it possible to show like this?

 

Regards,

Suvendu

1 REPLY 1
Stu_SAS
SAS Employee

The quickest approach would be to add a binary flag to your data indicating the end of the month. For example:

 

data want;
    set have;
    flag_end_of_month = (date = intnx('month', date, 0, 'E') );
run;

You can then use this to create a custom category in Visual Analytics that creates your filter. Or, you can name it 'End of Month' directly in the code. 

 

Alternatively, you can create this flag directly in Visual Analytics with some more manual logic:

if(   (month('Date'n) in(1,3,5,7,8,10,12)  AND dayofmonth('Date'n) = 31)
   OR (month('Date'n) in(4,6,9,11)         AND dayofmonth('Date'n) = 30)
   OR (month('Date'n) = 2                  AND dayofmonth('Date'n) in(28*(1-'Leap Year'n), 29*'Leap Year'n) )
   )
return 'End of Month'
else ' '

 

Where 'Leap Year'n is:

if(    (mod(year('Date'n), 4)   = 0 AND mod(year('Date'n), 100) > 0)
OR (mod(year('Date'n), 400) = 0 AND mod(year('Date'n), 100) = 0)
)
return 1
else 0

Which we get from the standard leap year formula:

  • The year is evenly divisible by 4 and the year is not evenly divisible by 100 or;
  • The year is evenly divisible by 400 and evenly divisible by 100

Thank you for posting this question - this is a great use case for expanding date/time functions within Visual Analytics to do these types of calculations. 

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 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
  • 256 views
  • 0 likes
  • 2 in conversation