Expression Help

sona_sa — Tue, 25 Jun 2019 07:04:29 GMT

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,

topic Expression Help in QlikView

Expression Help