Hi Team,
I want Year wise Calculation: Below is Script, data and Expression:
Script:
Options1:
Load dual(Options1,Num) as Options1 Inline [
Options1, Num
Millions, 1000000
Full value, 1
];
Options2:
Load dual(Options2,Num) as Threshold Inline [
Options2, Num
'1 M $', 1000000
'2 M $', 2000000
'3 M $', 3000000
];
Option3:
Load * Inline [
Option3, Option3.Table
Yes, cost_plus _ex_cc
No, cost_plus
Yes, transfer_price_ex_cc
No, transfer_price
];
DATA:
Sub_Account | Cost_Plus | Cost_Plus_CC | transfer_price | transfer_price_excc | Year | Month |
Expense | 30000000 | 578300000 | 179506000.9 | 273592000 | 2019 | Jan |
Expense | 31000000 | 624100000 | 179507000 | 274559000.3 | 2019 | Feb |
Expense | 34100000 | 782400000.9 | 199181000 | 280056000.7 | 2019 | Mar |
Expense | 34500000 | 796700000.1 | 204975000.3 | 302410000.4 | 2019 | Apr |
Expense | 34800000.98 | 928000000 | 206196000.7 | 302797000.4 | 2019 | May |
Expense | 54300000 | 947600000 | 231799000 | 325057000.5 | 2018 | Jan |
Expense | 54700000.21 | 1023800000 | 237569000.7 | 325777000.1 | 2018 | Feb |
Expense | 57000000 | 1027500001 | 250111000.5 | 372404000.4 | 2018 | Mar |
Expense | 60800000 | 1063600000 | 272898000.2 | 388108000.4 | 2018 | Apr |
Condition:
If I select Option1 = 'Million ' and Option2 = '1M$' and Option3 = 'No'Then I want the sum of Cost_Plus, transfer_price, cost_plus_ex_cc and transfer_price_ex and --> Where Value will be in Million which is Greater Then 1M
If I select Option1 = 'Million ' and Option2 = '2M$' and Option3 = 'No'Then I want the sum of Non_PTI, transfer_price, cost_plus_ex_cc and transfer_price_ex and --> Where Value will be in Million which is Greater Then 2M
Expression:
IF(GetFieldSelections(Options1)='Millions',IF(GetFieldSelections(Options3)='No',Sum({<sub_account={'Expense'}>}cost_plus)/min(Options1), (Sum({<sub_account={'Expense'}>}cost_plus_ex_cc)/min(Options1)),
IF(GetFieldSelections(Options3)='No', Sum({<sub_account={'Expense'}>}cost_plus), Sum({<sub_account={'Expense'}>}cost_plus_ex_cc))),
IF(GetFieldSelections(Options1)='Millions', IF(GetFieldSelections(Options3) = 'No', SUM({<sub_account = {'Expense'}>}transfer_price)/min(Options1), (SUM({<sub_account = {'Expense'}>}transfer_price_ex_cc)/min(Options1)),
IF(GetFieldSelections(Options3)='No',sum({<sub_account = {'Expense'}>}transfer_price),sum({<sub_account = {'Expense'}>}transfer_price_ex_cc)))
Please help on this.
Regards,
AK
