Multiplying multiple variables against a field

[#1]

Hello,

I have two fields that I am multiplying against each other.  Each field is in two different tables but are synced through a unique identifier field.   When I multiply them I have the following basic formula.

sum(KM*(Multi/100))

My problem is that for each row of the KM field I want to multiply a different variable against it.   I used dynamic update to do this originally but cannot use it on a server.  I want to give the ability to basically change the KM field.

My first attempt was the following with each variable.

sum(Multi*(KM/100))
*(
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Alberta486',300,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016British_Columbia486',122,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Saskatchewan486',80,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Quebec486',3,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Northwest_Territories486',0,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Ontario486',3,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Manitoba486',40,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016New_Brunswick486',0,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Nova_Scotia486',0,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Yukon486',0,
10))))))))))
)

Note: 300 in the first if statement would actually be a variable and each other number below would be a variable as well that the user could change.

The problem here is that unless I selected one of the strings in that unique identifier field it would just multiply everything by 10.  Which I don't want.  It would work, but I had to select as an example 'ConstructionGas2016Alberta486' and it would only work for that one.

I then though why not just create a specific set analysis statement for each row instead of the if statement which I tried the following.

300*sum({<PhaseTypeYearProvinceNAICS={'ConstructionGas2016Alberta486'}>}KM*(Multi/100))

This worked a bit better but would still give me data for provinces that were not associated with Alberta as an example. 

There are 600 rows in the KM field with a variable already created to adjust it.  Originally the variables were changed as a number you wanted to add or subtract from the "baseline" value in the KM field and then you a created a dynamic update script that would add/subtract the table based on updated variables.

Any ideas on how to replace the dyanmic update method would be extremely helpful!!

Thanks

[sunny_talwar · Accepted solution]

May be try bringing the if statement within the Sum and see what happens:

Sum(Multi*(KM/100)
*(
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Alberta486',300,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016British_Columbia486',122,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Saskatchewan486',80,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Quebec486',3,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Northwest_Territories486',0,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Ontario486',3,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Manitoba486',40,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016New_Brunswick486',0,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Nova_Scotia486',0,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Yukon486',0,
10))))))))))
))

[marcus_sommer]

I think Sunny is right - here a small adjustment to his suggestion:

sum(Multi*(KM/100)
*(pick(match(PhaseTypeYearProvinceNAICS, 'ConstructionGas2016Alberta486', 'ConstructionGas2016British_Columbia486', 'ConstructionGas2016Saskatchewan486','ConstructionGas2016Quebec486','ConstructionGas2016Northwest_Territories486','ConstructionGas2016Ontario486', 'ConstructionGas2016Manitoba486','ConstructionGas2016New_Brunswick486','ConstructionGas2016Nova_Scotia486','ConstructionGas2016Yukon486', '*'),
$(v1), $(v2), $(v3), $(v4), $(v5), $(v6), $(v7), $(v8), $(v9), $(v10), $(v11))

- Marcus

[#4]

Yes, Sunny's suggestion is working now.  Thank you very much! 

Marcus, I have never seen that formula before.  After looking at it, it makes sense to me though.

Does this run faster than having all the nested if statements?

One reason I like the nested if statements is that I can set a final else-if statement for all other rows in that column to go to zero without having to write in all 600 formulas for each row in like it seems I would have to do with the pick match method.

[sunny_talwar]

Pick is usually better to understand, smaller in length and you can def. set a final false condition like this

Pick(Match(FieldName, 'Value1', 'Value2') + 1, 'FalseValue', 'TrueValue1', 'TrueValue2')

The above is equivalent to this

If(FieldName = 'Value1', 'TrueValue1',

     If(Fieldname = 'Value2', 'TrueValue2', 'FalseValue'))

[marcus_sommer]

Pick(match()) will definitely be faster than a nested if-loop which calculates at first all branches (no break after the first true-result like in other tools) and evaluates afterwards which branch contained the true-value. I'm not sure how match() will be processed but you save at least the condition-part which will be executed only once.

- Marcus

[#7]

I ended up going with the pick match method because I also hit the nested if loop maximum.  It was roughly around 100 - figured it after removing several at a time until it worked - and I needed about 300.

Pick match is working perfectly!!

Thanks again, very much appreciated!

[#8]

I am having a problem again.  I have updated all my expressions, and when double checking the functionality the charts no longer update when changing the variable now that I am using the Pick Match method it seems.

As an example:

My expression includes this variable:

$(ceDemandGasConstruction)

Which in turn is defined in my script as:

SET ceDemandGasConstruction = sum(Multi*(KM/100)*(Pick(Match(PhaseTypeYearProvinceNAICS,

'ConstructionGas2015Alberta486',

'ConstructionGas2016Alberta486',

'ConstructionGas2017Alberta486',

'ConstructionGas2018Alberta486',

'ConstructionGas2019Alberta486',

'ConstructionGas2020Alberta486',

'ConstructionGas2021Alberta486',

'ConstructionGas2022Alberta486',

'ConstructionGas2023Alberta486',

'ConstructionGas2024Alberta486',

'ConstructionGas2025Alberta486',

'*'),

$(KMConstructionGas2015Alberta486),

$(KMConstructionGas2016Alberta486),

$(KMConstructionGas2017Alberta486),

$(KMConstructionGas2018Alberta486),

$(KMConstructionGas2019Alberta486),

$(KMConstructionGas2020Alberta486),

$(KMConstructionGas2021Alberta486),

$(KMConstructionGas2022Alberta486),

$(KMConstructionGas2023Alberta486),

$(KMConstructionGas2024Alberta486),

$(KMConstructionGas2025Alberta486))));

I then have input boxes with each of these variables.

But as stated, now the chart is not updating as the variables are updated. 

What am I missing??

[sunny_talwar]

Would you be able to share a sample (with dummy data) to see the issue?

Thanks,

Sunny

[#10]

Attached is a demonstration.

I recreated most accurately how I am doing it in my actual document.  For some reason it does not update when I use a variable to include the expression.

Any ideas?