Calculated dimension : Test country of geo-hierarchy

[Former Member]

Hi All,

In Lumira 1.29, i want to create a calculated dimension based on country and quantity and make this kind of formula:

if {Country} = "France" then {Quantity} * 1.1 else

if {Country} = "Germany" then {Quantity} * 1.2 else

if {Country} = "Italy" then {Quantity} * 0.9

else {Quantity}

I always get Quantity as result.

Note that Country is not a "normal" dimension. It is derived from an other dimension, Agency, as a geographic hierarchy dimension.

Thank you for your help,

Jacques

[Former Member · Accepted solution]

Hello Jacques

The condition doesn't work because the value in the geo-hierarchy is not "France" or "Italy". It's something like "2000001,NAVTEQ".

You can see this value by creating another calculated dimension of Country.

Then you can see the value in the prepare tab

You've to use this value in your formula.

[Former Member · Accepted solution]

Thank you, Sylvain

That's the kind of answer I expected

Regards,

Jacques

[Former Member]

1. Thank you SAP for removing my first post. It that your way with doing uncomfortable questions?

2. I have the same problem right now, because I need to compare the country based on the geo-hierarchy with another country in my table.

It would be easy if I could copy the text of the Geo-dimension to a regular dimension. But this copies only the underlying key-value.I do not understand that "feature" in Lumira, even a 20 year old Excel has the options to choose between values and formula.

@Sylvain:

I don't think this is very user friendly. Imagine to lookup and copy the NAVTEC values of all possible (200+) countries in a formula! That's definitely not expedient.

Regards,

Stefan

[Former Member]

Hello Stefan,

For my requirement, I have downloaded NAVTEQ list in an excel sheet, made the mapping on relevant Country descriptions and merged the dataset in Lumira.

I know it's boring to do that, but once the file is ready, you can re-use it for other use cases.

By my side, I only had few countries to map ;o)

Hope it helps,

Jacques

[TammyPowlas]

Jacques - I don't think this is possible; see Ludek's answer here: Calculated dimension based on a measure | SCN

[Former Member]

Hi Tammy,

Thank you for your answer.

But if I replace Country with Agency in the formula, it works.

For instance:

if {Agency} = "Nantes" then {Quantity} * 1.1 else

if {Agency} = "Berlin" then {Quantity} * 1.2 else

if {Agency} = "Milano" then {Quantity} * 0.9

else {Quantity}

Quantity for Berlin is multiplied by 1.2

Jacques