Aggregated measure not showing value when applying filter on object (list or Cross table)

[anandsasbi]

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

[Lodewijk]

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]

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

[Lodewijk]

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]

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 .