I have sales reps in my rows and qty as measure and product level in columns.
I wanted to show the sales reps who has sold a quantity of less than 100 for the products.
So if my product level contains 5 products, I should compare the qty value with respect to each product sold by the sales man and if all five are less then display the sales man.
SalesRep data item will have an expression like
Filter(SalesRepLevel,Tuple(product1,Qty)<50 And Tuple(product2,Qty)<50.
How should I check for all the product level when used with Tuple.
Thanks,
Nithya
In this case you don't need to reference the product level at all. A simple:
filer(salesRepLevel,qty<100) will return all sales reps who have less a quantity less than 100.
By adding Products to the columns, the crosstab will show the qty for each product for each salesRep.
But, let's say, for the sake of argument, that you have 6 products but you only care about the 5. So in this case, each rep might have more than 100 qty, because of Product 6. So you'll need to exclude that from the filter.
So to begin, create the item (myProducts) showing the 5 products. set(P1,P2,P3,P4,P5) or except(productLevel,P6)
Now you can filter the sales reps using: filter(salesRepLevel,total(qty within set myProducts)<100)