
Re: Calculated field in Table
Ian McGivern Apr 23, 2015 10:53 PM (in response to Janilson Andrade)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 frontend  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).

Re: Calculated field in Table
Xavier Retaillaud Apr 24, 2015 3:18 AM (in response to Ian McGivern )Icon(?) : Very nice feature. I didn't notice it. Thanks.


Re: Calculated field in Table
Toni Kautto Apr 24, 2015 2:24 AM (in response to Janilson Andrade)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;
 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)  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.

Community 161260.qvf 176.0 K

Re: Calculated field in Table
Kamil Lewandowski Mar 18, 2016 9:38 AM (in response to Toni Kautto )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.
 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.

Re: Calculated field in Table
Xavier Retaillaud Apr 24, 2015 3:22 AM (in response to Janilson Andrade)Hi Janilson,
For the PDF documents : http://help.qlik.com/sense/enus/pdf/
Especially : Script Syntax and Chart Functions Guide.pdf

Re: Calculated field in Table
Janilson Andrade Apr 24, 2015 8:18 AM (in response to Janilson Andrade)Thanks all for replies.
@Toni Kautto, 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

Re: Calculated field in Table
Toni Kautto Apr 26, 2015 2:05 AM (in response to Janilson Andrade)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 ) )


Re: Calculated field in Table
Janilson Andrade Apr 26, 2015 7:47 PM (in response to Janilson Andrade)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.

Re: Calculated field in Table
Janilson Andrade Jun 18, 2015 4:06 PM (in response to Janilson Andrade)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.
