If Statment/expression in Custom Mode

Tips and tricks
1

Hi,
Is there a way in Custom Mode to create an “if statement” that would return a value if True or false. the value can be as simple as a 1 and 0.
What I am looking to find out is if there has been a sales for product a and product b (maybe up to 20-30 products) in a period then I would like the expression to show 1 or “Yes” and if not 0 or “No”.
We have defined groups of individual products that we see as a core range for particular customers. There are also have a few different lists of products that make a different core range (For example Retail vs Trade)
The end results would hopefully show by customer whether they have bought the entire core range for the period.
Thanks in advance

2 Likes
2

Would love if there was a way to do if statements.

3

This can be done now using a filtered calculation and a bit of “pass through SQL”.

  1. Whilst in the customer dimension, enter custom mode and create a new calculated column. Add a measure item to it.
  2. On the ‘Period’ tab of the measure item, choose an offset month period so that the month is dynamic.
  3. On the ‘Selections’ tab, choose your product dimension and then tick the products you want to include.
  4. if you set the calculation to be just ‘a’ - it will list the sum value of the selected products for the selected customers. Which is close. But if you start the expression with tilde ‘^’ (shift-6 on a standard keyboard), then you can use any SQL functions, as long as they return a numeric value. If you use the expression;

^CASE WHEN {a} > 0 THEN 1 ELSE 0 END

image
image.png870×322 12.4 KB

Any customers who’ve bought it the period will show a ‘1’ and all the others will show ‘0’.

image

You can see in this example, that the first two lines have a value in the month, but obviously didn’t buy those core products, while the next three lines presumably did.

3 Likes
4

Thanks Jason. That looks great, but it does not quite work as I was hoping. It will return a value of 1 at the customer level if they have sales of 1 of the list of the products where I would like to return only the value of 1 if they have sales of all of the list.

Is this possible using something similar?

Thanks again

5

I have tried the following for 3 products and it seems to work.

^CASE WHEN {a} > 0 AND {b} > 0 AND {c} > 0 THEN 1 ELSE 0 END

It means I will have to add more parts to the formula a, b, c etc but I might get the answer that way. I will also have to check to see how may parts (a, b, c etc) the expression will allow

Unless anyone else has a more streamlined way of building the expression

1 Like
6

Probably not the answer your looking for, but the easiest option would probably be to dump the data into Excel and run your statements.

7

Hi @scott.williams - Thanks for the response, but I am looking to have Phocas show the results so the users do not have to dump anything out into Excel or something else.

8

Hi Patrick,

It might make sense to group the products as a separate dimension. That way your custom mode calculation would only require one measure. The group of products could be designated in your ERP and added to the extract, or it could be listed in a file and added to your database design. Customer Success can help you with adding the dimension and creating the list of products.

9

Hi, Thanks for the reply. Part of the problem with this solution is that a product maybe in several “core groups” eg Retail or Trade if the product is across both groups.

10

I tried to make more measures to add to ^CASE WHEN {a} > 0 AND {b} > 0 AND {c} > 0 THEN 1 ELSE 0 END (eg d, e, f etc) but it looks like the number of measures stops allocating a letter after the 5th one (e). Does anyone know that this is the case. The list I have can be up to 50 or more products.

11

Does anyone know if there is a way to incorporate an ANY operator into the expression that might make it work?

12

Depending on what your needs are you could have multiple product or product-customer dimensions that could be used. See below as an example of what the file layout might look like:

Product No \ Retail Group \Trade Group
100 \ Retail Group 1 \ Trade Group 1
101 \ Retail Group 1 \ NULL
102 \ Retail Group 2 \ Trade Group 2
103 \ Retail Group 2 \ Trade Group 3
104 \ NULL \ Trade Group 3
105 \ Retail Group 3 \ Trade Group 4

You’d add Retail Group and Trade Group as dimensions under product. Then you could select your customer (or customers) and use the expression within custom mode to select on either Retail Group or Trade Group depending on the type of customer.

You can have some products in both a retail and trade group, or some products in just one or the other. Additionally, you can have products in different retail groups and different trade groups or assigned to just one of each. These groups could be assigned in excel (or other flat file) and then uploaded in order to add the dimensions.

Let me know if this is something you want to look into further.

1 Like