Based on my requirement I have to find out the previous month Amount for same year. I have to show Current month and previous month amount in List or Cross table. On Table I have applied filter ( MMMYY & DepartmentName) based on Parameter.

For This I have created new column by right click the column ‘Amount’ (Difference from Previous Period). When I am not using any filter on List table on Report canvas then both value showing in list once I am applying filter ( MMMYY & DepartmentName) the value for new column (Difference from Previous Period) is not coming.

Below code has been generated automatically for new column (Difference from Previous Period)

I think its aggregated column which not works when I am applying filter on object. Could you please suggest how can I calculate previous month value (not Aggregated measure) so I can easy use it in any object (list or cross table) where I have used filter.

If you filter the object for the current month only, you will not be able to calculate the difference with the previous month anymore, since the data of the previous month is not available (it is "removed" due to the filter). If the data for the current month and the previous month are different rows in the dataset, you need to do some aggregation anyway. Could you show an example of what you are trying to achieve in the table?

My Actual requirement to build SAS VA report based on attached report format. Need to show indicator (Red and Green Arrow key) based on comparison between current & previous month % value,

In report we need to pass date value (MMMYY) and department, In report I have to display the amount and % value for each Transaction type against Transaction Channel (Online, Mobile, Branch, Contact Centre ), Transaction Channel will verify based on Department ( 1 to 5 channel ) So I can’t use List table here because No of Column is not fixed.

So I want to put Previous and current month value in same row and BY use of Display rule at list I want to change the Background color of % value.( because Gauge not apply for Cross tab) and hide Previous month % column ( if possible )

Additional Question :- Suppose I have use 2 measure column in Crosstab but I want Total Row sum for only selected column ( in my case column #). Is possible In Crosstab?

Suppose below is source data and I want to create new Column Prev_% which will keep Last month % Value.

DEPLOY_DATE

Category

Transaction Type

Channel

%

#

Dec-15

Cash & Deposits

Cash Withdraw als

Branch

0.01

5431

Dec-15

Statements

Mini Statement

Digital_Mobile

0.41

6542

Dec-15

Simple service transactions

Redeem cheque

Digital_Online

0.02

2543

Nov-15

Cash & Deposits

Cash Withdraw als

Branch

0.01

5431

Nov-15

Statements

Mini Statement

Digital_Mobile

0.04

5342

Nov-15

Simple service transactions

Redeem cheque

Digital_Online

0.21

1543

Oct-15

Cash & Deposits

TD

Digital_Mobile

0.51

5843

Note:- As per current environment I can’t use SAS code or Store process , I have to play with Excel data and SAS VA Report Designer only