Sign InHelp
turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Discussion board where members can get started with Qlik Sense.

- Qlik Community
- :
- Qlik Sense
- :
- Qlik Sense Enterprise
- :
- Qlik Sense Enterprise Discussions
- :
- New to Qlik Sense
- :
- Calculated field in Table

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Showing results for

Not applicable

04-22-2015
03:19 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

Solved! Go to Solution.

7,027 Views

1 Solution

Accepted Solutions

Not applicable

06-18-2015
04:06 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

5 Views

9 Replies

kangaroomac

Contributor II

04-23-2015
10:09 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

tko

Employee

04-24-2015
02:24 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

Not applicable

04-24-2015
03:18 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

5 Views

Not applicable

04-24-2015
03:22 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Hi Janilson,

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

Especially : Script Syntax and Chart Functions Guide.pdf

Not applicable

04-24-2015
07:38 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

5 Views

tko

Employee

04-26-2015
02:05 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

)

)

5 Views

Not applicable

04-26-2015
07:47 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

5 Views

Not applicable

06-18-2015
04:06 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

6 Views

klewandowski

New Contributor III

03-18-2016
09:38 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

5 Views