Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Option1:
Load * Inline [
Option1
Million (Means will display the value in M)
Full Value (Means will display the value in Integer)
];
Option2:
Load * Inline [
Option2
'1 M $'
'2 M $'
'3 M $'
];
Now I have to make an expression for calculation on -
If I select Option1 = 'Million ' and Option2 = '1M$' Then I want the sum of Non_PTI, Where Value will be in Million which is Greater Then 1M
If I select Option1 = 'Million ' and Option2 = '2M$' Then I want the sum of Non_PTI, Where Value will be in Million which is Greater Then 2M
If I select Option1 = 'Million ' and Option2 = '3M$' Then I want the sum of Non_PTI, Where Value will be in Million which is Greater Then 3M
For --> sum({<account={'EXPENSE'}>}non_ pti)
Else Full Value in Integer. like --> 20000000000.25614
Can anyone make an expression with these 2 condition for me.
Regards,
AK
Thanks Vegar For Quick Solution.
Here for Full Value selection, It is not coming in full integer value. Like - 2.5 M (Full Value Means - 2500000)
Also one small doubts, is it work on Table, the same function.
Thanks Again.
AK
Hi Vegar I am trying to do the same, But Not Coming in Straight Table.
I am using 3 List box for the same.
Option1:
Load * Inline [
Option1
Million (Means will display the value in M)
Full Value (Means will display the value in Integer)
];
Option2:
Load * Inline [
Option2
'1 M $'
'2 M $'
'3 M $'
];
Option3:
Load * Inline [
Option3
Yes
No
];
If I select Option1 = 'Million ' and Option2 = '1M$' and Option3 = 'No'Then I want the sum of Non_PTI, 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, Where Value will be in Million which is Greater Then 2M
If I select Option1 = 'Million ' and Option2 = '3M$' and Option3 = 'No' Then I want the sum of Non_PTI, Where Value will be in Million which is Greater Then 3M
For --> sum({<account={'EXPENSE'}>}non_ pti)
Else Full Value in Integer. like --> 20000000000.25614
Can You make an expression with these 3 condition for me.
Please do the needful.
AK
Hi Vegar,
For Option3 - In Script What I need to do, Whatever We have done for Option1 and Option2 (Dual Function)
and
I am using for the same -->
(if(GetFieldSelections(Option3)='No', sum({<sub_account={'EXPENSE_TYPE'}>}non_controllable_pti)/min(Option1),
sum({<sub_account={'EXPENSE_TYPE'}>}non_controllable_pti_ex_cc)/min(Option1)))
Full Value in Integer. like --> 20000000000.25614 and IS it possible in Table?
It will work or not.
Regards,
AK
Hi Vegar,
I have used the same for my QVF. Below is my script :-->
Option1:
Load dual(Option1,Num) as Option1 Inline [
Option1, Num
Million (Means will display the value in M), 1000000
Full Value (Means will display the value in Integer), 1
];
Option2:
Load dual(Option2,Num) as Option2 Inline [
Option2, Num
'1 M $', 1000000
'2 M $',2000000
'3 M $',3000000
];
Option3:
Load dual(Option3,Num) as Option3 Inline [
Option3, Num
Yes
No
];
Now I want to use the same expression -->Sum({<Accout={'EXPENSE'}>}non_pti)/min(Option1)
In my expression -->
(if(GetFieldSelections(Option3)='No', sum({<sub_account={'EXPENSE_TYPE'}>}non_controllable_pti)/min(Option1),
sum({<sub_account={'EXPENSE_TYPE'}>}non_controllable_pti_ex_cc)/min(Option1)),
if(GetFieldSelections(Option3)='No', sum({<sub_account={'EXPENSE_TYPE'}>}non_controllable_pti), sum({<sub_account={'EXPSENSE_TYPE'}>}non_controllable_pti_ex_cc)))
It is not working.
Regards,
AK
I Created for the same with this help of this :-
But not working :-
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, non_controllable_pti_ex_cc
No, non_controllable_pti
Yes, controllable_pti_ex_cc
No, controllable_pti,
Yes, fee_spread_ex_cc
No, fee_spread
Yes, transfer_price_ex_cc
No, transfer_price
];
IF(GetFieldSelections(roundingOptions)='Millions',IF(GetFieldSelections(IFrsOptions)='No',sum({<sub_account={'EXPENSE_TYPE'}>}non_controllable_pti)/min(Options1),sum({<sub_account={'EXPENSE_TYPE'}>}non_controllable_pti_ex_cc)/min(Options1)),
IF(GetFieldSelections(IFrsOptions)='No',sum({<sub_account={'EXPENSE_TYPE'}>}non_controllable_pti),sum({<sub_account={'EXPSENSE_TYPE'}>}non_controllable_pti_ex_cc))),
IF(GetFieldSelections(roundingOptions) ='Millions',IF(GetFieldSelections(IFrsOptions) = 'No', Sum({<sub_account={'EXPENSE_TYPE'}>}controllable_pti)/min(Options1), Sum({<sub_account={'EXPENSE_TYPE'}>}controllable_pti_ex_cc)/min(Options1)),
IF(GetFieldSelections(IFrsOptions)='No',sum({<sub_account={'EXPENSE_TYPE'}>}controllable_pti),sum({<sub_account={'EXPENSE_TYPE'}>}controllable_pti_ex_cc))),
IF(GetFieldSelections(roundingOptions)='Millions',IF(GetFieldSelections(IFrsOptions)='No',Sum({<sub_account={'EXPENSE_TYPE'}>}cost_plus)/min(roundingOptions), (Sum({<sub_account={'EXPENSE_TYPE'}>}cost_plus_ex_cc)/min(Options1)),
IF(GetFieldSelections(IFrsOptions)='No', Sum({<sub_account={'EXPENSE_TYPE'}>}cost_plus), Sum({<sub_account={'EXPENSE_TYPE'}>}cost_plus_ex_cc))),
IF(GetFieldSelections(roundingOptions)='Millions', IF(GetFieldSelections(IFrsOptions)='No',SUM({<sub_account ={'EXPENSE_TYPE'}>}fee_spread)/min(Options1), (SUM({<sub_account ={'EXPENSE_TYPE'}>}fee_spread_ex_cc)/min(Options1)),
IF(GetFieldSelections(IFrsOptions) = 'No',sum({<sub_account ={'EXPENSE_TYPE'}>}fee_spread), sum({<sub_account ={'EXPENSE_TYPE'}>}fee_spread_ex_cc))),
IF(GetFieldSelections(roundingOptions)='Millions', IF(GetFieldSelections(IFrsOptions) = 'No', SUM({<sub_account = {'EXPENSE_TYPE'}>}transfer_price)/min(Options1), (SUM({<sub_account = {'EXPENSE_TYPE'}>}transfer_price_ex_cc)/min(Options1)),
IF(GetFieldSelections(IFrsOptions)='No',sum({<sub_account = {'EXPENSE_TYPE'}>}transfer_price),sum({<sub_account = {'EXPENSE_TYPE'}>}transfer_price_ex_cc)))
Can you help on the same.
Regards,
AK
Hi Vegar,
Below is data, Can you make the same expression : In the previous attached updated version.
IF(GetFieldSelections(roundingOptions)='Millions',IF(GetFieldSelections(IFrsOptions)='No',Sum({<sub_account={'EXPENSE_TYPE'}>}cost_plus)/min(roundingOptions), (Sum({<sub_account={'EXPENSE_TYPE'}>}cost_plus_ex_cc)/min(roundingOptions)),
IF(GetFieldSelections(IFrsOptions)='No', Sum({<sub_account={'EXPENSE_TYPE'}>}cost_plus), Sum({<sub_account={'EXPENSE_TYPE'}>}cost_plus_ex_cc))),
IF(GetFieldSelections(roundingOptions)='Millions', IF(GetFieldSelections(IFrsOptions) = 'No', SUM({<sub_account = {'EXPENSE_TYPE'}>}transfer_price)/min(roundingOptions), (SUM({<sub_account = {'EXPENSE_TYPE'}>}transfer_price_ex_cc)/min(roundingOptions)),
IF(GetFieldSelections(IFrsOptions)='No',sum({<sub_account = {'EXPENSE_TYPE'}>}transfer_price),sum({<sub_account = {'EXPENSE_TYPE'}>}transfer_price_ex_cc)))
Sub_Account | Cost_Plus | Cost_Plus_CC | transfer_price | transfer_price_ex_cc | 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 |
Hi Vegar, Below is data and expression : can you convert the same logic in your updated qvf,
IF(GetFieldSelections(roundingOptions)='Millions',IF(GetFieldSelections(IFrsOptions)='No',Sum({<sub_account={'EXPENSE_TYPE'}>}cost_plus)/1000000, (Sum({<sub_account={'EXPENSE_TYPE'}>}cost_plus_ex_cc)/1000000),
IF(GetFieldSelections(IFrsOptions)='No', Sum({<sub_account={'EXPENSE_TYPE'}>}cost_plus), Sum({<sub_account={'EXPENSE_TYPE'}>}cost_plus_ex_cc))),
IF(GetFieldSelections(roundingOptions)='Millions', IF(GetFieldSelections(IFrsOptions) = 'No', SUM({<sub_account = {'EXPENSE_TYPE'}>}transfer_price)/1000000, (SUM({<sub_account = {'EXPENSE_TYPE'}>}transfer_price_ex_cc)/1000000),
IF(GetFieldSelections(IFrsOptions)='No',sum({<sub_account = {'EXPENSE_TYPE'}>}transfer_price),sum({<sub_account = {'EXPENSE_TYPE'}>}transfer_price_ex_cc)))
Sub_Account | Cost_Plus | Cost_Plus_CC | transfer_price | transfer_price_ex_cc | 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 |
Please do the needful.
Thanks & Regards,
AK