BookmarkSubscribeRSS Feed
anuragraishines
Obsidian | Level 7

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 

 

 

3 REPLIES 3
SASKiwi
Opal | Level 21

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
Obsidian | Level 7

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
Opal | Level 21

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); 

 

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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