Show End of Period Values

[Suvi2020]

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_1	Category_lvl_2	LOB	Value	End_of_Month
Cat1	Sub_cat1	LOB1	21	31-Jan-20
Cat1	Sub_cat3	LOB1	19	31-Jan-20
Cat1	Sub_cat2	LOB2	31	29-Feb-20
cat2	Sub_cat3	LOB1	24	31-Jan-20
cat2	Sub_cat4	LOB2	54	29-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

[Stu_SAS]

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.