02-25-2016 08:16 PM
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?
03-01-2016 12:22 PM
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.
03-07-2016 11:36 AM
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...
03-07-2016 12:45 PM
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.