Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
hkg_qlik
Contributor III

Front end expression - Multiplier

How can we create a new Field on UI (QS Table) without making any changes on back end script. Month and Value 1 are available.

Now user wants to derive Value 2 field as shown below.

Value 2 (Feb) = Jan(Value 1) * Feb(Value 1) = 1.08*1.09 = 1.18

Value 2 (Mar) = Feb(Value 1) * Mar(Value 1) = 1.04*1.18 = 1.22

Value 2 (Apr) = Mar(Value 1) * Apr(Value 1) = 1.03*1.22 = 1.26

MonthValue 1Value 2
Jan1.08 
Feb1.091.18
Mar1.041.22
Apr1.031.26

 

Thanks.

Labels (1)
3 Replies
Channa
Valued Contributor III

Re: Front end expression - Multiplier

above(Value1)*Value1

 

above(above(Value1)*Value1)*Value1

 

if(Month='Feb',above(Value1)*Value1,above(above(Value1)*Value1)*Value1)

 

this will help you

 

Channa
Partner
Partner

Re: Front end expression - Multiplier

aggr(sum({<Month>}Value1)*above(sum({<Month>}Value1)),Month)

aggr : even if the order of the months is altered, the result of the above will always be the same :

I mean, month 1 is above month 2, if u change the order in the table, u could have month 3 above month 2 (order desc), without the aggr by Month, the result would be incorrect

<Month> : if u select a Month, u'll have only one line left in ur table but with the correct result

 

see this in action:

Capture.PNG

Now I change the sorting order:

with Aggr: same results 

Capture.PNG

without Aggr : incorrect results:

I select a Month : with the <Month> : correct result

Capture.PNG

without the <Month> : No results: Qlik cannot see the above Month:

Capture.PNG

Hope this helps !

 

EDIT: 

Haven't paid attention:

here's the correct answer:

if(Month=2,
aggr(sum({<Month>}Value1)*above(sum({<Month>}Value1)),Month),
sum(Value1)
*above( Column(1)))

 

Capture.PNG

 

Omar BEN SALEM

Channa
Valued Contributor III

Re: Front end expression - Multiplier

if(Month='Feb',above(Value1)*Value1,above(above(Value1)*Value1)*Value1)

 

 

you will get attached results

if you have problem adding Month filter just add some key to ur database

just for 2 nd row

Channa