Expression Editor for 1M, 2M and 3M

[sona_sa]

Hi Team,

I have a scenario, My current expression is below:

if(GetFieldSelections(Options1)='Millions', if(GetFieldSelections(Options2)='No', sum({<account={'EX'},Date={'$(=vTime1)'}>}non_pti)/1000000, sum({<account={'EX'},Date={'$(=vTime1)'}>}non _pti_cc)/1000000)

Now I want to add another if condition where a dimension is Options3 --> 1M$, 2M$ and 3M$.

1. If User select 1M$, Then from the current expression >1M$ amount of record should to select Else the Current Expression as it is.
2. If User select 2M$, Then from the current expression >2M$ amount of record should to select Else the Current Expression as it is.
3. If User select 3M$, Then from the current expression >3M$ amount of record should to select Else the Current Expression as it is.

Now How and where I can put my third option in this expression.

Please help me on the same.

Regards,

AK

[Vegar]

You could solve this using column/dimension limits in your chart/table.

https://help.qlik.com/en-US/sense/April2019/Subsystems/Hub/Content/Sense_Hub/Visualizations/Table/ta...

[sona_sa]

Hi Vegar,

Accepted, But still the confusion :

Where I have to put the Options3 condition in my Expression so it will work and fulfill the requirements :

if(GetFieldSelections(Options1)='Millions', if(GetFieldSelections(Options2)='No', sum({<account={'EX'},Date={'$(=vTime1)'}>}non_pti)/1000000, sum({<account={'EX'},Date={'$(=vTime1)'}>}non _pti_cc)/1000000)

1. If User select 1M$, Then from the current expression >1M$ amount of record should to select Else the Current Expression as it is.
2. If User select 2M$, Then from the current expression >2M$ amount of record should to select Else the Current Expression as it is.
3. If User select 2M$, Then from the current expression >2M$ amount of record should to select Else the Current Expression as it is.

Regards,

AK

[Vegar]

I would declare options3 like this:

Options3:
LOAD dual(txt, value) as Options3 
INLINE [
txt, value
1M$, 1000000
2M$, 2000000
3M$, 3000000
];

Then in the dimension limit I would limit using larger or equal to en exact value using this expression. Exclude others.

=Only(Options3)

The expression Only(Options3) will return NULL() when no or multiple values are selected, this will result in no dimension reductions. If you do not want to rely on this default behaviour, then you should probably write an expression using a special clause when no or multiple Options3 values are selected.