How to use subfield inside a calculation

[sersal10]

Hi all,

I need to create a table of the regions where a product has been sold most and it will be in percentage of the total sales of the product.

For cities instead of region, I used this that worked:

Sum({<customer_country = {*}>} quantity)
/ Sum({<customer_country = {*} >} total quantity)

But I created an id of city-region-country called unique_id because there are names of cities shared between countries, and I get those names fine to create a pie chart using 

=SubField(unique_id_province, '-', 2) and it worked fine, so this way I reach the name of the province I want to use to create the table.

I think I need to use =SubField(unique_id_province, '-', 2) in Sum({<customer_country = {*}>} quantity)
/ Sum({<customer_country = {*} >} total quantity) somehow.

Any help?

Many thanks,

Sergio.

[BrunPierre]

Try

=Sum({<customer_country={"$(=SubField(unique_id_province,'-',2))"}>} quantity)

...

[sersal10]

Hi BrunPierre,

The thing is that Customer_country is a field I have but I do not want to use now. I want to extract the name of the province of the field calle unique_id and make the table summing up the quantity field of those rows.

Any further explanation just ask me.

Many thanks for your help

[sersal10]

I am trying this but there is a syntax error. like a "," or a ")" is expected

Sum({<unique_id_province = {"*-" & SubField(unique_id_province, '-', 2) & "-*"}>} quantity)

[marcus_sommer]

Calculated dimensions have a lot of disadvantages against native fields. Therefore just avoid the trouble and create all needed fields within the data-model.

[BrunPierre]

Can you provide sample data along with the expected outcomes?

[sersal10]

I will try another way. Thanks anyway

[sersal10]

I will try another way. Thanks anyway