Calculated field in Table

[#1]

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)
A	20
B	30
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

[kangaroomac]

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.

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).

[ToniKautto]

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.

[#4]

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

[#5]

Hi Janilson,

For the PDF documents : http://help.qlik.com/sense/en-us/pdf/

Especially : Script Syntax and Chart Functions Guide.pdf

[#6]

Thanks all for replies.

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

What I need is something like this:

Dim1	Value (From Database)	What I need
A	10	10
B	20	20
C		A+B (30)
D	50	50
E		C*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]

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
  )
)

[#8]

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.

[#9 · Accepted solution]

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]

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.