BookmarkSubscribeRSS Feed
WKruk
Fluorite | Level 6

Hello VA Users, 

 

I use VA 7.1 version.

 

I have built a crosstab as below. Each customer is assigned to one salesman. However, in each year the assignment can change.

 

                            year

                                2013         2014      2015

customer 1               4            23                15

customer 2               7            35                20

customer 3               9             6                 9

customer 4               7           12                 21

 

customer 1 Salesman_X Salesman_X Salesman_X 

customer 2 Salesman_X Salesman_Y Salesman_Y 

customer 3 Salesman_Y Salesman_Y Salesman_X 

customer 4 Salesman_X Salesman_X Salesman_Y 

 

I need to filter the table to get customers who are assigned to the Salesman_X in 2015 but I would like to remain results for 2013, 2014 and 2015. So the result table should remain rows with customer 1 and 3. When I try to filter the table by year (2015) and the Assignment  (Salesman_X ) I get customers 1 and 3 but columns with 2013 and 2015 disappear.

 

I have no idea how to filter rows by conditions for a specific year. I have also tried to use a combination of drop down lists and parameters but with no success. 

 

Would you have any  suggestions?

 

 

3 REPLIES 3
Renato_sas
SAS Employee

Hi WKurk,

 

You may be able to accomplish that by using parameters in calculated item expressions. For example, suppose you have two control objects (e.g. dropdown lists), one for salesman and one or year. Now assign each one of those controls a different parameter (e.g. pSalesman and pYear). You could create a calculated item like this (I have not checked the syntax, btw, but it should be close enough):

 

If 'pYear'p = Year and 'pSalesman'p = Salesman

return measure

else .

 

That would still show all years (missing values on 2013 and 2014), and for 2015 only those that are assigned to the selected salesman. If you want to preserve the original values for 2013 and 2014, you could change the expression logic, for example:

 

If 'pYear'p = Year

return (if 'pSalesman'p = Salesman return measure else .)

else measure

 

I hope it helps.

 

Best,

Renato

WKruk
Fluorite | Level 6

Hello Renato,

thank you for your inspiration. However,  I have tried to use it, but I have problem with a syntax of the calculated item. There is a warning that I try to use different type of data in If...else formula and bad combination of text and numeric data...

 

Regards!

 

 

Renato_sas
SAS Employee

If you make sure you are comparing data items of the same type and your expresion is returning the same data type as defined by the calculated item, you should not have problems.

 

Best,

Renato

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 2086 views
  • 2 likes
  • 2 in conversation