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.
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?
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
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 .)
I hope it helps.
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...
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.