Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

akumar_c
Contributor

Help in Expression with Condition

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

1 Reply
saar_lorena
New Contributor III

Re: Help in Expression with Condition

Hi!

Can you please explain your issue better?

I couldn't understand what you need.

 

Thanks