How to filter rows in a table by an individual column's filters?

[WKruk]

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?

[Renato_sas]

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]

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]

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