Compare current month vs previous month SAS Viya

[harshpatel]

Hello Folks,

I want to compare current year with previous year dynamacially in SAS Viya

Actually i have a drop down list of finyear

2018-19

And in my list view i will have some columns

ColumnA        Current year          Previous year

Rice                 20000                    1000

Wheat              10000                    500

Oil                     500                       200

Now suppose if from drop down list someone will change the filter to 2019-20 

then the comparision should be between 2019-20 with 2018-19

Regards,

Harsh Patel

[PetriRoine]

Hello @harshpatel ,

Could you please describe how your "source data" is laid out, especially finyear is interesting? Does it look something like this?

Best regards,

Petri

[harshpatel]

Yes Petri,
Exactly like this

[Sam_SAS]

Hello @harshpatel, 

It sounds like you want to compare the data for a given month with the same month from a different year. The basic calculation for this is easy to do by using the ParallelPeriod operator:

https://documentation.sas.com/?cdcId=vacdc&cdcVersion=8.5&docsetId=vareportdata&docsetTarget=p0dovqq...

Unfortunately, it is not so easy if you want a drop-down list to control which year's data is compared. I am not sure if it is possible to do this while also keeping a dynamic frame of reference.

Maybe someone else can offer a way that this can be done.

Something you can do is have a selector that would pick "Number of years ago": 1 year ago, 2 years ago, and so on. Based on the value of this control, you could have a calculated item that would return different ParallelPeriod calculations for each selection.

Sam

[PetriRoine · Accepted solution]

I believe it's doable. Let's go!

Here's two pics of what I created:

And here are the steps I took to achieve this.

Step 1: Create New data items

1. Parameter: _par_fiscal_year , type is Character. To this parameter we will store the user selection from Drop-down list.
2. Calculated item: Year (current), Result Type is Date, Format is Year4 (Year). Here we parse the parameter _par_fiscal_year to get the current year the end user wants in his comparison table. We will use Year (current) later on to subset the data to two years that we will induce from Drop-Down list selection.
   

   DateFromMDY(1, 1, Parse(Substring('_par_fiscal_year'p, 1, 4),
   'COMMA32.'))

   
   
3. Calculated item: Year (previous), Result Type is Date, Format is Year4 (Year). Here we parse the parameter _par_fiscal_year to get the previous year the end user wants in his comparison table. We will use Year (previous) alongside Year (current) later on to subset the data to two years that we induce from Drop-Down list selection.
   

   DateFromMDY(1, 1, ( Parse(Substring('_par_fiscal_year'p, 1, 4),
   'COMMA32.') - 1 ))

   
   
4. Calculated item: My Year, Result Type is Date, Format is Year4 (Year). Now we subset the data to two years based on rule "if user selection is 2019-20 then the comparison should be between 2018 and 2019".
   

   IF ( ( 'Date (Year)'n = 'Year (current)'n ) OR ( 'Date (Year)'n =
   'Year (previous)'n ) )
   RETURN 'Date (Year)'n
   ELSE .

Step 2: Drop-down box

1. Category = finyear, Parameter = _par_fiscal_year, Check in Required from Options pane

Step 3: Crosstab

1. Columns: My Year, Rows: Col A, Measures: Measure
   Filter: 

   ( 'My Year'n NotMissing )

Please try this and let me know how it went.

Best regards,

Petri

[harshpatel]

Thank you so much Petri,

It works perfectly for me