Logic Help

[sona_sa]

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

[Vegar]

You could solve it like this. Please see my attached qvf.

[sona_sa]

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

[sona_sa]

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

[sona_sa]

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

[sona_sa]

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

[Vegar]

OK, 

try this updated version 

[sona_sa]

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

[sona_sa]

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

[sona_sa]

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