How to multiplicate two measures

[Alessandro87]

Hi!

I have some measures: sum(production) that gives for example 0.00001587 and BTC_today that gives the EUR value of BTC in real time.

In a table I have as dimension: users, product etc... then as measures: sum(production), BTC_today

I have to show also the value in EUR fro the production, so I have created a new measures: sum(production)*BTC_today but it's not giving the expected result but only null values, why?

I have also tried by creating master measures items, but nothing change.

Any suggestion?

Thanks

[mpc]

Hi, 

If BTC_today is a Master Item, you should use [BTC_today] to use it. 

Regards

[marcus_sommer]

Make sure that BTC_today is a valid number and not a string and that these data are properly associated to the used dimensions and measure-field.

Further you didn't apply an aggregation to this field-call which would lead to NULL if there isn't just a single value available. In this case you may try: sum(production * BTC_today)

[Fabiano_Martino_Intelco]

Hello @Alessandro87 ,

a possible explanation is the presence of more than one value for BTC_today for a specific set of dimension.

Could you post a sample of your data (in Excel) and the structure of the table?

Regards

Fabiano

[Alessandro87]

Hi,

actually I have found out that the field that I use to create the master item BTC_today is loaded as text instead of a numeric value.

Already tried num() and num#() function but is not changing, instead it became null().

Here the script code involved:

RestConnectorMasterTable:

SQL SELECT 

"__KEY_root",

(SELECT 

"__FK_bpi",

"__KEY_bpi",

(SELECT 

"code" AS "code_u1",

"symbol" AS "symbol_u1",

"rate" AS "rate_u1",

"description" AS "description_u1",

"rate_float" AS "rate_float_u1",

"__FK_EUR"

FROM "EUR" FK "__FK_EUR")

FROM "bpi" PK "__KEY_bpi" FK "__FK_bpi")

FROM JSON (wrap on) "root" PK "__KEY_root";

[EUR]:

LOAD [code_u1] AS [code_u1],

[symbol_u1] AS [symbol_u1],

[rate_u1] AS rate_u1,

[description_u1] AS [description_u1],

[rate_float_u1] AS [rate_float_u1],

[__FK_EUR] AS [__KEY_bpi]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_EUR]);

DROP TABLE RestConnectorMasterTable;

[Final_rent]:

NoConcatenate

Load *

Resident rent;

Join 

load 

    NUM ( DUAL (Text( [rate_u1]  ) , '# ##0.00' )) as rate_u1

Resident EUR;

Drop table rent;

Drop table EUR;

Without any transformation rate_u1 is as text field, then if I try to modify it became null()

[marcus_sommer]

Your approach isn't correct - neither logically nor syntactically. You need to apply:

num#(YourField, 'TheRightFormatPattern')

The specified format-pattern must fit to 100% to the string-content and it must relate to the settings within the interpretation-variables (usually at the beginning of the script).

In some cases it might be more practically to apply left/right/mid-string-functions to cut the relevant parts and/or also some replace() to remove currency-sign or thousand-delimiters or change the decimal-delimiter from comma to dot or ...

[Alessandro87 · Accepted solution]

Thank you Marcus.

Now the field is loaded as number, in a table I have this value 63685087 that it is not formatted as needed of course. It should be 63685,09

How can I do it? (please note that from the rest connection I get 3 decimals)

Thanks

Fixed:

for who maybe need it:

Num#(Replace(Replace([rate_u1], ',', ''), '.', ','), '#.###,##') AS rate_u1

then in front end custom number as #0,00

Now it is displayed as 63685,09

[marcus_sommer]

You may just divide the value, like: round(Field / 1000, '0.001')