topic Re: Calculate Percent Change in VA in SAS Visual Analytics

Calculate Percent Change in VA

ghartge — Tue, 25 Sep 2018 21:47:42 GMT

Greetings everyone!

Is it possible to calculate the percent change between to values in a SAS VA crosstab? I have an example attached which I could make more straightforward but the way the data is displayed is what was requested. I would like to calculate the percent difference between for example the value for 2017 Female Asian (36) and the 2016 Female Asian value (61) and all other values down the columns.

((36 - 61) / (61))*100 = 40.98%

The data driving this report is student level meaning one row per student which is grouped and totaled by SAS using distinct count on a dummy id text variable.

Thanks,

Gary

Re: Calculate Percent Change in VA

mleitson — Thu, 04 Oct 2018 13:29:11 GMT

Hi Gary,

I've been trying to figure out a solution as well. Did you find a way?

Re: Calculate Percent Change in VA

ghartge — Thu, 04 Oct 2018 14:09:12 GMT

Hey ML,

No responses/solutions yet. Several views but it seems this isn't possible in VA.

Gary

Re: Calculate Percent Change in VA

Robert_T — Thu, 04 Oct 2018 19:23:36 GMT

You must create another aggregated measure using parallel period operator(s) along with some simple numeric operators.

Assuming that your existing "Headcount" aggregated measure is calculated as: Distinct [_ByGroup_] ('Row_ID'n)

Then your percent difference called "Headcount (% Diff)" would be calculated as:

( ParallelPeriod(_DistinctCount_, 'Row_ID'n, _ApplyAllFilters_, 'Year'n, _Inferred_, _Inferred_, 0, _Full_, {Date}) - ParallelPeriod(_DistinctCount_, 'Row_ID'n, _ApplyAllFilters_, 'Year'n, _Inferred_, _Inferred_, -1, _Full_, {Date}) ) / Abs(ParallelPeriod(_DistinctCount_, 'Row_ID'n, _ApplyAllFilters_, 'Year'n, _Inferred_, _Inferred_, -1, _Full_, {Date}))

Set the format of this new aggregated measure to Percent, and drag it into the cross tab.

Hope this works for you.

Re: Calculate Percent Change in VA

ghartge — Thu, 04 Oct 2018 20:25:19 GMT

Thank you Robert_L,

Can you give me a bit of clarity on the 'Year'n value? I substituted the variables I use ('Term Year'n) and get the error(s) below. This is a character field.

( ParallelPeriod(_DistinctCount_, 'Student_ID'n, _ApplyAllFilters_, 'Term Year'n , _Inferred_, _Inferred_, 0, _Full_, {Date}) - ParallelPeriod(_DistinctCount_, 'Student_ID'n, _ApplyAllFilters_, 'Term Year'n, _Inferred_, _Inferred_, -1, _Full_, {Date}) )

Abs(ParallelPeriod(_DistinctCount_, 'Student_ID'n, _ApplyAllFilters_, 'Term Year'n, _Inferred_, _Inferred_, -1, _Full_, {Date}))

Invalid data item for this context: Term Year

Thank you,

Gary

Re: Calculate Percent Change in VA

Robert_T — Thu, 04 Oct 2018 21:17:59 GMT

I'm guessing that your variable 'Term Year'n is not formatted as a date with the year specified. The data item for the period calculation must be an item whose format specifies year.

http://support.sas.com/documentation/cdl/en/vaug/66720/HTML/default/viewer.htm#n1lxnqfip132can1hdzuerib7i76.htm

Re: Calculate Percent Change in VA

ghartge — Mon, 29 Oct 2018 14:12:25 GMT

Hey Robert_T (or anyone),

This "seems" WAY more complicated than it should be. In the attached image I describe what I am trying to do.

I have a category titled "Days From the 1st Day of Classes" that are my row values.

I have two semesters (Term_Year) that is a measure as columns. They are '2017' and '2018'.

I "simply" need to calculate the percent difference for each row.

2017 2018

Days From Headcount Headcount

42 15,576 15,587 Need percent change here

as the third column

The user doesn't want dates they need the number of days pre and post the first day of classes and the percent (and actual number) difference for each day. I cannot do this in EG since this data will be linked to a drop-down list for several different categories of students. The last day this runs there will be about 100 rows and the data will have interactions with a line graph and possibly a pie chart.

Days From the First Day of Classes - Category, Format Float

Headcount - Aggregated Measure - Distinct [_ByGroup_] ('Student_ID'n)

Term_Year - Category - Format $

Thanks!

Gary

Re: Calculate Percent Change in VA

kanchans — Sun, 24 Feb 2019 09:36:29 GMT

hi,

I am stuck with exactly the same problem where I am trying to find % difference between the last two quarters of aggregated measure which I have calculated like this

Sum [_ByGroup_] ('twoplusatsix'n) / Sum [_ByGroup_] ('onbookatsix'n)

and for the year variable I have the date variable which has quarter and year in it

can someone please tell me how should I apply the formula for my aggregated measure to find the % difference

Re: Calculate Percent Change in VA

ccaulkins912 — Mon, 29 Jul 2019 20:19:47 GMT

!mleit, #ghartg_e, et al. What @Robert_L said and linked to makes sense, if you're using the part of VA that he and others seem to think you are. @ccaulkins91, @ccaulkins9, #ccaulkins912

Re: Calculate Percent Change in VA

ccaulkins912 — Mon, 29 Jul 2019 20:21:28 GMT

@kanchans, If you were to re-post this as a new ticket you might be able to get some more feedback. Feel free to keep trying although it looks like you;'re no longer using SAS Communities? too bad...