Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
shahafei2
Creator
Creator

Pick(Match()) in Pick(Match()) or Pick(Match(aggr()))

Hi all,

I'm having an issue with the pick(match()) function

i'm loading a table as part of the script that contains two ranking fields per product

Product,Rank A,Rank B

 

I'm presenting a straight table chart

when i'm using both fields as a dimensions the expression result is correct (See expression below) - (Rank B is hidden)

But the  straight table dimension is still duplicated 

 

The expression that i'm using when i use both fields as dimensions:

pick(match([Rank B],'AA','A','B','C','D','E','F'),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_AA),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_A),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_B),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_C),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_D),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_E),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_F))

 

Could i use this expression in a way that the "Rank B" field will be hidden and that the table will present the grouped data by "Rank A" field? only 

 

Otherwise, There is a way to use the pick(match()) function with an aggregation function, if so how to use it in that case?

 

Thanks

 
12 Replies
shahafei2
Creator
Creator
Author

Yes

 

marcus_sommer

I think I was with my suggestion to the expression-simplification by nesting the pick(match()) within the variable a bit too quickly because the syntax-approach isn't quite correct and even more worse this kind of $-sign expansion creates an adhoc-variable which won't be calculated on row-level else globally and could therefore not work in your case.

This means you need really a separate picking of the right variable like:

pick(match([Rank B],'AA','A','B','C','D','E','F'),
    $(vABCD_Co_A), $(vABCD_Co_B), $(vABCD_Co_C), $(vABCD_Co_D), $(vABCD_Co_E), $(vABCD_Co_F))

Beside this the reason why you not got the expected results is that the matching of the variables to the rank isn't applied on the proper level. This means the dimensions within the aggr() which define the context in which the matching needs to be applied doesn't fit.
As far as your data have more then a single value within the rank B in regard to your rank A the [Rank B] within match() will return NULL - and I assume that will be quite likely in your case. Therefore needs the dimensions-list within the aggr() to be on the lowest level in which the values are definitely unique. Because you mentioned the ranks are applied to products I took Product as a dimension - but if there are sub-products or the ranking changed over any period-fields or anything else it needs to be included there.

sum(aggr(
   sum({<Line_Type={'1','2','3'}>} Daily_Avg_Sales) / 
   pick(match([Rank B],'AA','A','B','C','D','E','F'),
       $(vABCD_Co_A), $(vABCD_Co_B), $(vABCD_Co_C), $(vABCD_Co_D), $(vABCD_Co_E), $(vABCD_Co_F)),
Product, Period))

- Marcus

shahafei2
Creator
Creator
Author

thanks