Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Month | Value 1 | Value 2 |
Jan | 1.08 | |
Feb | 1.09 | 1.18 |
Mar | 1.04 | 1.22 |
Apr | 1.03 | 1.26 |
Thanks.
above(Value1)*Value1
above(above(Value1)*Value1)*Value1
if(Month='Feb',above(Value1)*Value1,above(above(Value1)*Value1)*Value1)
this will help you
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:
Now I change the sorting order:
with Aggr: same results
without Aggr : incorrect results:
I select a Month : with the <Month> : correct result
without the <Month> : No results: Qlik cannot see the above Month:
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)))
Omar BEN SALEM
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