topic Re: Compare current month vs previous month SAS Viya in SAS Visual Analytics

Compare current month vs previous month SAS Viya

harshpatel — Mon, 01 Jun 2020 09:52:19 GMT

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

Re: Compare current month vs previous month SAS Viya

PetriRoine — Mon, 01 Jun 2020 10:03:14 GMT

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

Re: Compare current month vs previous month SAS Viya

harshpatel — Mon, 01 Jun 2020 10:07:14 GMT

Yes Petri,
Exactly like this

Re: Compare current month vs previous month SAS Viya

Sam_SAS — Mon, 01 Jun 2020 14:18:33 GMT

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=p0dovqqp325peyn1dpijpxxmwj83.htm&locale=en#p1dn6xullue9hkn131dkuudpz2br

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

Re: Compare current month vs previous month SAS Viya

PetriRoine — Wed, 03 Jun 2020 05:31:00 GMT

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

Parameter: _par_fiscal_year , type is Character. To this parameter we will store the user selection from Drop-down list.
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.'))
```
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 ))
```
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

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

Step 3: Crosstab

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

Re: Compare current month vs previous month SAS Viya

harshpatel — Wed, 03 Jun 2020 04:38:01 GMT

Thank you so much Petri,

It works perfectly for me