BookmarkSubscribeRSS Feed
SKG
Obsidian | Level 7 SKG
Obsidian | Level 7

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

3 REPLIES 3
Renato_sas
SAS Employee

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

SKG
Obsidian | Level 7 SKG
Obsidian | Level 7

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

Renato_sas
SAS Employee

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

 

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
  • 3 replies
  • 1722 views
  • 0 likes
  • 2 in conversation