Calculated Metric for last two months

[anuragraishines]

Hi,

I am really don't how to achieve this or really this is possible in SAS VA 7.3. After watching SAS VA 8.2 I really don't like 7.3 because of it limitations. Anyways quick help or workaround in this will be of great help.

In Metric 4 (M4) It is the sum of M1, M2 and M3 till the 3rd last month of the selected till date which simple but things gets messy for calculation of last two months. To calculate M4 for las two months it should take the average of M5 last 3rd and 4th month and than add M1+M2+M3. I am stuck on average part and how to show different calculations in same metric. This has been achieved in qlikview but unfortunately I don't have the calculation logic.

Service Month	3/1/2019	4/1/2019	5/1/2019	6/1/2019	7/1/2019	8/1/2019
M1	100	100	100	100	100	100
M2	75	65	12	10	10	5
M3	10	15	5	20	10	5
M4	M1+M2+M3	M1+M2+M3	M1+M2+M3	M1+M2+M3	AVERAGE(1.20,1.00)+M1+M2+M3	AVERAGE(1.20,1.00)+M1+M2+M3
M5	1.50	6.50	1.20	1.00	2.90	2.90
			AVERAGE

I know it is little messy thing but any help on this will be highly appreciated.

Thanks,

Anurag 

[SASKiwi]

How is your source data structured? If you start with one row of data per month, the easiest way is to add a month index variable which you set to 0 for the current month, 1 for the last month, 2 for the month before that and so on.

Then to get the sum or average of the last three months, create an aggregate measure in the VA report designer, and filter on the month index <= 2. 

[anuragraishines]

Hi,

I have never used index functionality with SAS VA. A little more insight will be helpful.

I have multiple rows for the same period and data has to aggregate dynamically to the dimension used.

I am able to create both the metric but how to show them in single metric. The real challenge is to show two different metric calculation in single row.

Regards,

Anurag

[SASKiwi]

You can derive a month counter this way, assuming that the latest reporting date is the last month and is the last day of each month (eg 31 May 2019). Note this logic requires a SAS DATA step and should be part of your data preparation before loading into LASR.

  * Derive reporting month counter, latest month = 0, previous month = 1 and so on.;
  Reporting_Month = intck('MONTH', Reporting_Date, "&today"d);