topic Re: ADD one more Option in Expression in QlikView

ADD one more Option in Expression

sona_sa — Sat, 16 Nov 2024 03:10:41 GMT

I have scenario, Like this.

I have 3 List box.

Option 1 - Million and Full, When User is selecting Million, Value is coming like 1.1 M, 1.45 M and When User is Selecting Full, Value is coming 1000000.45, 1010000 ...... in Straight Table

Option 2 - No and Yes, When User is selecting No, Some Value is adding and When user is Selecting Yes, Value is extracting in Straight Table

Both is working well.

Now I have to add one more option, i.e - Option 3 - 1M, 2M and 3M, Suppose User select the 1M, Now I want only greater than 1M value record in my straight table.

Similarly for 2 Million and 3 Million. But my option 1 and option 2 should also work with Option 3.

Below is my Expression :

IF(GetFieldSelections(Option1)='Millions', IF(GetFieldSelections(Option2)='No',

sum({<sub_account={'EXPENSE'}>}Amount)/1000000, (Sum({<sub_account={'EXPENSE'}>}Amount_Ex_CC - Amount_Ex))/1000000),

IF(GetFieldSelections(Option2)='No', sum({<sub_account={'EXPENSE'}>}Amount),(Sum({<sub_account={'EXPENSE'}>}Amount_Ex_CC - Amount_Ex))))

This is single expression.

Can anyone add new option 3 in my above expression.

Regards,

Re: ADD one more Option in Expression

chrismarlow — Wed, 26 Jun 2019 19:02:11 GMT

Hi,

I would take a different approach to simplify your expressions.

From the toy script at the bottom you get a disconnected table that lets you use Only(Divisor) to divide as your Option1 replacement. You would just put that at the end. Strength here is when someone says can you also do thousands … you just add to the table.

To impose the limit you could again have a Limit table disconnected (or maybe even just a variable) & use dimension limits.

You still use your IF statement for the other option, but would hopefully be easier to follow.

Cheers,

Chris.

divisor: LOAD * INLINE [ Option1, Divisor Millions, 1000000 Units, 1 ]; limit: LOAD * INLINE [ Limit 1000000 2000000 3000000 ]; data: LOAD * INLINE [ Dim, Mea A, 1000001 B, 2000001 C, 3000001 ];

Re: ADD one more Option in Expression

sona_sa — Thu, 27 Jun 2019 06:57:03 GMT

Hi Chrismarlow,

Is it not possible to make any expression which I have written for Option1 and Option2. So If I am using Option 3, How I can put that condition in my expression.

IF(GetFieldSelections(Option1)='Millions', IF(GetFieldSelections(Option2)='No',

sum({<sub_account={'EXPENSE'}>}Amount)/1000000, (Sum({<sub_account={'EXPENSE'}>}Amount_Ex_CC - Amount_Ex))/1000000),

IF(GetFieldSelections(Option2)='No', sum({<sub_account={'EXPENSE'}>}Amount),(Sum({<sub_account={'EXPENSE'}>}Amount_Ex_CC - Amount_Ex))))

Regards,

Re: ADD one more Option in Expression

sona_sa — Thu, 27 Jun 2019 10:51:56 GMT

Hi Chrismalow

Is it possible by expression, Below is required information:

I have a listbox : 1M, 2M and 3M, Created by Inline function, Below is script :

Option:
Load Inline [
Option, Num
'1 M $', 1000000
'2 M $', 2000000
'3 M $', 3000000
];

I have a straight table, Where All the possible value is displaying.

Now I want when I select 1M$, only greater than 1M value should to display in Straight Table.

When I select 2M$, only greater than 2M value should to display in Straight Table, Similarly to 3M.

How I can do the same: Below is my Expression.

If (GetFieldSelections(Option1)='Millions', if(GetFieldSelections(Option2)='No', Sum({<account={'EXPENSE'}>}Amount)/1000000, Sum({<sub_account={'EXPENSE_TYPE'}>}Amount_CC)/1000000),
if(GetFieldSelections(Option2)='No', Sum({<sub_account={'EXPENSE'}>}Amount), Sum({<sub_account={'EXPENSE'}>}Amount_CC))))

Can you feed the logic for the same.

Thanks,

Re: ADD one more Option in Expression

sona_sa — Thu, 27 Jun 2019 18:10:56 GMT

Is there any solution for the same.

Regards,

Re: ADD one more Option in Expression

chrismarlow — Thu, 27 Jun 2019 18:38:38 GMT

Hi,

So if you don't want to use the dimension limits but are happy to use the limit table per your post then your pattern involving the least repetition will be;

if(sum(Mea)>only(Limit),Sum(Mea),Null())/only(Divisor)

Slotting in your functions I think would give;

if(if(GetFieldSelections(Option2)='No', Sum({<account={'EXPENSE'}>}Amount), Sum({<sub_account={'EXPENSE_TYPE'}>}Amount_CC))>only(Num),if(GetFieldSelections(Option2)='No', Sum({<account={'EXPENSE'}>}Amount), Sum({<sub_account={'EXPENSE_TYPE'}>}Amount_CC)),Null())/if (GetFieldSelections(Option1)='Millions',1000000,1)

Which will only work when you have something selected on Option table.

Cheers,

Chris.

Re: ADD one more Option in Expression

sona_sa — Fri, 28 Jun 2019 07:37:00 GMT

Lots of thanks. 😊

Re: ADD one more Option in Expression

chrismarlow — Fri, 28 Jun 2019 12:49:21 GMT

You are welcome.