BookmarkSubscribeRSS Feed
anandsasbi
Obsidian | Level 7

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)

RelativePeriod(_Sum_, ' Amount 'n, 'DEPLOY_DATE'n, _Inferred_, 0, _Full_, {Date}) - RelativePeriod(_Sum_, ' Amount 'n, 'DEPLOY_DATE'n, _Inferred_, -1, _Full_, {Date})

 

I have replace '_SUM_ ' and  '_Inferred_' By '_Average_' and '_ByMonth_' respectively. but nothing change in result

RelativePeriod(_Average_, '%'n, 'DEPLOY_DATE'n, _ByMonth_, 0, _Full_, {Date}) - RelativePeriod(_Average_, '%'n, 'DEPLOY_DATE'n, _ByMonth_, -1, _Full_, {Date})

 

 

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.

 

Regards,

Anand

 

 

4 REPLIES 4
Lodewijk
Obsidian | Level 7

Hi Anand,

 

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?

 

Best regards,

Lodewijk

 

 

anandsasbi
Obsidian | Level 7

Thanks Lodewijk for Quick reply 🙂

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 😞

 

 

Regards,

Anand


MyReportFormat.png
Lodewijk
Obsidian | Level 7

Hi Anand,

 

I think the aggregated measures are the most likely way to achieve this, given the dataset. Filtering on DEPLOY_DATE won't work, since that will remove previous month values.

 

An option is to create an aggregated measure, something like:

IF 'DEPLOY_DATE'n = 'Selected Date'p
RETURN Sum [_ByGroup_] ('#'n)
ELSE .

 

This returns the selected month only, and you can use the same logic to find the parallel period as you did earlier.

Be sure to check out this article of you want to use dates in parameters in VA: Date parameters in SAS VA.

 

Hope this helps!

- Lodewijk

 

 

anandsasbi
Obsidian | Level 7

Thanks for reply, I am using same way for use Date value in parameter. I am looking for any function which can return previous month data value (like INTNX(‘Month’, Parse(‘Date’p,’ANYDTDTE9.’,-1)).

Becouse i need to find out last month value where other column details sould be same as current records .

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 4 replies
  • 1793 views
  • 0 likes
  • 2 in conversation