Exploring, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

How to display current and previous years data on corsstab

Reply
Contributor SKG
Contributor
Posts: 55

How to display current and previous years data on corsstab

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

SAS Super FREQ
Posts: 76

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

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:

 

Table1.PNG

 

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):

 

Table2.PNG

 

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:

 

Table3.PNG

 

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:

 

Table4.PNG

 

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

 

Table5.PNG

 

Best,

Renato

Contributor SKG
Contributor
Posts: 55

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

Posted in reply to Renato_sas

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".

SAS Super FREQ
Posts: 76

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

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

 

Ask a Question
Discussion stats
  • 3 replies
  • 420 views
  • 0 likes
  • 2 in conversation