If(Match with a Range of Values

[cgT]

Hi there,

I'm looking to build an if(Match expression that picks values from multiple ranges. Something like this:

=if(match(TransactionDate,'>=18/02/2022<=13/03/2022','>=22/08/2022<=18/09/2022','>=17/02/2023<=13/03/2023'), if(BkiGrossAmount > 0, TransactionWeekCommencing))

Of course, this isn't returning any values.

Can someone help please?

Many thanks, 

[vamshi2i]

Hi CgT,

Let's say you have a field called "Sales" and you want to categorize the sales into three groups based on different ranges:

1. Low: Sales <= 1000
2. Medium: 1000 < Sales <= 5000
3. High: Sales > 5000

You can create a new field called "SalesCategory" using the following expression in the load script or in a calculated field:

try this:

LOAD
Sales,
if(Match(Sales, 0, 1000, 5000), // Check if the Sales value matches any range
if(Match(Sales, 0, 1000), 'Low', // Check if Sales <= 1000
if(Match(Sales, 1000, 5000), 'Medium', 'High' // Check if 1000 < Sales <= 5000
)
)
) as SalesCategory; // Assign the appropriate category based on the matched range

[Aurelien_Martinez]

Hi,

You can try 

If(not IsNull(Only({<TransactionDate={">=18/02/2022<=13/03/2022", ">=22/08/2022<=18/09/2022", ">=17/02/2023<=13/03/2023"}>} TransactionDate)),
  //your expression
 )

[cgT]

Thanks @Aurelien_Martinez ,

This is what I have. However, the expression editor is saying that it's ok as an expression, but when I enter it the dimension box returns an 'invalid dimension' error message..

=If(not IsNull(Only({<TransactionDate={">=18/02/2022<=13/03/2022", ">=22/08/2022<=18/09/2022", ">=17/02/2023<=13/03/2023"}>} TransactionDate)), if(BkiGrossAmount > 0, TransactionWeekCommencing))

[cgT]

Thanks @vamshi2i ,

however, I'm not looking to create elements in the Load Script, as I just want to effect a single calculated dimension for a single visualisation.

Thanks,

[Aurelien_Martinez]

If it's a calculed dimension, you need to add a Aggr function. Maybe you need to ajust the last param 

=Aggr(If(not IsNull(Only({<TransactionDate={">=18/02/2022<=13/03/2022", ">=22/08/2022<=18/09/2022", ">=17/02/2023<=13/03/2023"}>} TransactionDate)), if(BkiGrossAmount > 0, TransactionWeekCommencing)), TransactionWeekCommencing)

[cgT]

Unfortunately this is return a blank chart also although it is accepted as a valid dimension.

[Aurelien_Martinez]

Can you share a Excel file with a little example ?