expression referring to columnlabels and expressions

[curiousfellow]

I have a chart containing dimension:linenumber and

two columns labeled column1 and column2

I want to add a third column containing the difference between column1 and column2 OR an expression

My expression for column3 = if(linenumber = 110, [column1]-[column2]) this works fine.

Another expression for column3 is : if(linenumber = 220, expression with variables) this works fine too

But when I want to merge this two expressions into one expression I get an error and one of the "(" turns red

My new expression is like this:

If (linenumber = 110,column1]-[column2],

if (linenumber = 220, expression with variables))

Is it not possible to use both ways of calculation in one formula, and if not, is there an alternative not using complete formula's ?

I am using Qlikview May 2021 SR1

[RafaelBarrios · Accepted solution]

Hi @curiousfellow 

it is possible

only error i see is that your are missing a braket (highlighted in red)

If (linenumber = 110,[column1]-[column2],

if (linenumber = 220, expression with variables))

also i guess you have column1, column2 and column3 as master measures and you could have problems because of data model or aggregation level on each measure.

remember that you can use the values of the columns by simply calling them within the same object using column(number), this will only considere measure columns and not dimensions columns.

my third column measure is:

if(Date>=makedate(2023,11,01),column(1)-column(2),
if(Date<makedate(2023,11,01),column(2)))

Hope this helps,

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 

[curiousfellow]

Thank you very much, the bracket was a typo, but using column(number) did the trick.

[RafaelBarrios]

Great @curiousfellow !!!

im glad it works for you...

Keep in mind that this solution has an important "BUT", and that is that users can reorder the columns and this can cause the table to fail.

Best regards,