Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculated field in Table

Hi all.

I'm new in Qlik Sense. That's the first time I post here. I have what I think is a newbie question.

I have created a table with two field (example as below):

Field 1(Dim)      

Value (measure sum)

A20
B30
C

I know I can use IF conditional to put some value in "C", like if Field 1 = C then 0. But I need to make C receive the value from expression C = A + B = 50.

How Can I make it in Qlik Sense? I though I could use set analysis but it seems not the way to go, since it reflects in the whole chart/table and not only in some row we want to calculate.

I have searched a lot and I found nothing, it seems like I would need to make some subquery to get the values from a specified row.

One last question, where can I get the list from all functions that I can use in Qlik Sense? Maybe the answer for this question is in one or more functions.

Thanks in advance

1 Solution

Accepted Solutions
Not applicable
Author

I solved it using set analysis

if( Variavel = 'RB', sum({<Variavel={"A"}>} Valor),

        if( Variavel = 'A', sum({<Variavel={"A"}>} Valor), 0)

)

This way, to each row I use a set analisys to calculate the value I want to show.

Thanks for all that tried to help me.

View solution in original post

9 Replies
kangaroomac
Partner - Creator II
Partner - Creator II

Hi Janilson,

Not sure I understand your question, but when you load your table, if C is always A + B you can load (in the script loading section in the Load Editor) it as:

"MyTable":

LOAD

     A,

     B,

     A + B As C

From SourceTable;

Your other option (if I understand your question correctly) is to use A + B (or Sum(A + B)) as an Expression in the front-end - which means you don't have to load C.

Regarding functions, you can use the help menu.

One way to see what functions there are in Qlik Sense is to go to the Community Home Page and Click on Qlik Sense.

Qlik Sense Help.png

If you search for Functions in the Search Box, it will take you to a page that contains all (I assume) the functions available in Qlik Sense.

Another nice feature I found is that if you are in the Load Editor for instance, you can click the help icon (?) which will make all functions in the script content sensitive to the help (fancy way of saying if you click on the link the help menu will show content specific to it).

Help Menu for Functions.png

ToniKautto
Employee
Employee

The first thing to find is the actual measure that gives you the expected value of 50. I could see two ways of defining this;

  1. The total sum over all dimension values. This should work if C is assumed to be zero then if can be included in the aggregation.

    Sum(TOTAL Data)

  2. Aggregate all values not associated with C. This could be done by using a set expression with the E() function, which will include all Dim values that were excluded by selecting C. This can be a useful approach in scenarios were you want to exclude data for a specific value.

    Sum({<Dim=E({<Dim={"C"}>})>} TOTAL Data)

If you add a Data value for Dim=C, you will find that the set expression ignores this value while the TOTAL aggregation includes it. The aggregation choice will now depend on what exactly you are trying to calculate.

By combining the C value expression in a If() function you can customize your measure to aggregate differently depending on the dimension value.

Not applicable
Author

Icon(?) : Very nice feature. I didn't notice it. Thanks.

Not applicable
Author

Not applicable
Author

Thanks all for replies.

@, I have imported your example (thanks), it does work but it is not what I need.

What I need is something like this:

Dim1Value (From Database)What I need
A1010
B2020
CA+B (30)
D5050
EC*D = 50*30 = 1500

I need to make some calculations in some rows, I know that C is A+B, but I will have to use this result to calculate E (C*D).

I can't make it in load script because in my real application I will need to get some values selected in some filter [1]

[1] - example: numbers of months selected - if it is greater than some value I will use some formula, and if it is not, I will need to use a diferent formula.

How could a resolve it with my "logic"? I would use some variables, but for that I would have to get the value of some row, for example:

IF DIM='C' THEN SET VARIABLE C = (VALUE FROM DIM WHERE DIM='A') + (VALUE FROM DIM WHERE DIM='B')

Of course I know this sintax is wrong but the logic is something like this.

I hope my question is more clear now, sorry for not explaining well.

Thanks again for all replies

ToniKautto
Employee
Employee

I will not be able to give you a perfect answer in this case, as I am not able to analyze and evaluate your business needs and requirements.

I think that you can resolve your new example by the same principles as I suggested in my example. This would however require a nested IF statement to trigger the third alternative expression for your E value. Generally nested IF statements in not a recommended approach as it easily can add calculation over head to you chart.

If your data has predictable positions and the dimension sort order is static you can use the Above() or other inter record functions to pick values in the chart. Dim C can be calculated with for example a RangeSum() of the above partial sums. The RangeSum() for C can also be calculated from position E by just targeting 2 rows above. In your example the below expression should be an applicable solution.

if( Dim<>'C'AND Dim <> 'E', Sum(Data),

  If( Dim='C', RangeSum( Above(  Sum(Data),0, RowNo(TOTAL) ) ), // C is aggregated as a range sum of all above rows.

      Above(Sum(Data),1)* // Dim = D, one row above

      RangeSum( Above(  Sum(Data),2, RowNo(TOTAL) )) // Rangesum in position C, 2 rows offset

  )

)

Not applicable
Author

Hi Toni, I think it is the way to get what I want.

I've just tried and it didn't work =(  It's just a test,

Here is a printscreen of my real example:  http://i.imgur.com/Nna7JJR.jpg 


I've have just get some values and put in RB and A row, so I get get it in field RL  I expected to get the value 2 in RL row, since the above row is B, and has "ordem" = 2. 


The code I've used in "TESTE" field is: 


if( Variavel = 'RB', 33    //first row gets 33

   if(Variavel = 'A', sum(Ordem),     //Second row gets sum Ordem = 1

      if(Variavel = 'B', sum(Ordem),  //Third gets 2

         if( Variavel = 'RL', above(sum(Ordem, 1)), //RL should get the value of above Ordem, in this case it would be 2, but it doesn't work

            666     //666 for the rest of values

         )   

     

  

)  I don't know if there's something more to do.  Thanks for advice.

Not applicable
Author

I solved it using set analysis

if( Variavel = 'RB', sum({<Variavel={"A"}>} Valor),

        if( Variavel = 'A', sum({<Variavel={"A"}>} Valor), 0)

)

This way, to each row I use a set analisys to calculate the value I want to show.

Thanks for all that tried to help me.

Anonymous
Not applicable
Author

Thanks Toni for this trick. Unfortunetely I need to have this in my script. Of course I cannot write any aggregation function in there. So how am I supposed to is your solution in script.

Thanks in advance for help.