topic Re: How to display current and previous years data on corsstab in SAS Visual Analytics

How to display current and previous years data on corsstab

SKG — Thu, 08 Sep 2016 08:47:41 GMT

Hi Guys,

Actually I'm facing a problem in SAS VA.

Target Dataset coming in SAS VA like this: An Example

Current_Date Previous_Date(Current_Date - 364 days) Current_Product_Name Current_Revenue Previous_Product Previous_Revenue

08 Aug 2016 07 Aug 2015 A 4 A 10

08 Aug 2016 07 Aug 2015 A 5 A 20

08 Aug 2016 07 Aug 2015 A 8 B 3

08 Aug 2016 07 Aug 2015 A 9 C 3

08 Aug 2016 07 Aug 2015 B 2 B 1

08 Aug 2016 07 Aug 2015 B 4 A 2

08 Aug 2016 07 Aug 2015 C 4 A 2

when i'm going to use crosstab in designing level and drag these variables then i'm getting like this

here's filter of current_date :- 08 Aug 2016

Current_Product_Name Current_Revenue Previous_Revenue

A 26 30

B 6 1

C 4 0

(Missing) 0 10

--------------------------------------------------------------------------------------------

Total 36 41

But my requirement is:

here's filter of current_date :- 08 Aug 2016

Current_Product_Name Current_Revenue Previous_Revenue

A 26 34

B 6 4

C 4 3

---------------------------------------------------------------------------------------------

total 36 41

Could anyone please help me as soon as possible that how can i resolve this problem on VA level?

I'll really thankful for your.

Please help me on the same.

Regards,

Sunny Kumar Gupta

Re: How to display current and previous years data on corsstab

Renato_sas — Wed, 21 Sep 2016 23:29:42 GMT

Hi @SKG,

I think I have a solution, but you will need to make some changes in your source table. Before we start, I'd like to make a correction: 08 Aug 2016 - 364 is 10 Aug 2015 (not 07 Aug 2015 as provided in your example). Having said that. I'm using the corrected date in my example below.

First, you need to restructure your input table as below:

Then you create an auxiliary calculated column that adds 364 to the Date column (you could have that column added to your source table if you want):

Next, I assume you have a way to select a date (e.g. 08-Aug-2016) for filtering. You need to assign that date to a parameter. I've already shared with you in another thread an article that should help solve this step (please see Date parameters in SAS Visual Analytics), and derive a date calcualted column (e.g Selected_Date) from that parameter. This is also explained in the article. This is what you should have now:

The last step is to derive current and previous revenues. It would be something like this:

Curr_Rev:

if 'Selected_Date'n = 'Date'n

return Rev

else .

Prev_Rev:

if 'Selected_Date'n = 'Date+364'n

return Rev

else .

This would give you the table below:

Now you should have all elements necessary to display and aggregated table that looks like what you want:

Best,

Renato

Re: How to display current and previous years data on corsstab

SKG — Fri, 23 Sep 2016 05:30:54 GMT

Hi Renato,

Thank for helping but if i want to select Date in Slider "10-Aug-2015" then what Selected Date for that? It could be a same selected date:- 8-Aug-2016

As per your method, you assigned selected date - 8-Aug-2016 for 8-Aug-2016 and 10-Aug-2015. I agreed but this selected date only for 8-Aug-2016 and 10-Aug-2015 but not for when i want to select "Selected Date: 10-Aug-2015".

Re: How to display current and previous years data on corsstab

Renato_sas — Fri, 23 Sep 2016 14:59:27 GMT

Hi SKG,

I'm sorry, but I don't think I understood your question. The calculated column Selected Date will contain the date that you selected from your control object.

Best,

Renato