BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
harshpatel
Quartz | Level 8

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

 

       

1 ACCEPTED SOLUTION

Accepted Solutions
PetriRoine
Pyrite | Level 9

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

 

Here's two pics of what I created:

Comm_Capture5.PNGComm_Capture6.PNG

 

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

View solution in original post

5 REPLIES 5
PetriRoine
Pyrite | Level 9

Hello @harshpatel ,

 

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

Comm_Capture3.PNG

 

Best regards,

Petri

harshpatel
Quartz | Level 8
Yes Petri,
Exactly like this
Sam_SAS
SAS Employee

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
Pyrite | Level 9

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

 

Here's two pics of what I created:

Comm_Capture5.PNGComm_Capture6.PNG

 

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
Quartz | Level 8

Thank you so much Petri,

It works perfectly for me

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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
  • 5 replies
  • 2536 views
  • 1 like
  • 3 in conversation