Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculated columns and tag object

Hello,

I have two questions:

Question 1:

How do you make calculated columns? Is it correct to do this in the script? I want to calculate the discount capex over the projects. something like this:

LOAD

sum([Capex] / ((1 + 0.1) ^ ([Year] - 2009))) as Discount Capex

SQL SELECT Capex,ProjectName

FROM

quintus2009.dbo.ProjectPortfolio

GROUP

BY ProjectName ;

But I get an error about missing '(' ???

Question 2:

Is it possible to create a tag object in Qlikview. With the tag object you can create temporally an selection of values which are in scope. example: scope In / Out

You can use the tag object in graphics

Regards,

Wouter Dijkstra

6 Replies
johnw
Champion III
Champion III

I will often use calculated columns in the script when the columns can be calculated at the lowest level of detail. The reason here is performance and simplicity - write and execute it once at load time rather than writing and executing it over and over on different tables.

I will usually NOT use calculated columns in the script when the columns are at a grouped level of detail. The reason here is flexibility - I don't want to lock the application into ONLY being able to display that calculation for that particular grouping. I tend to change groupings routinely, and don't want to keep modifying my script every time I add or remove a field from a chart. That is particularly true when I make dynamic charts that the user can modify.

I'm not quite following what you're trying to accomplish with the tag object.

Not applicable
Author

Thanks for your answer. Where do you use/implement calculated columns in Qlikview when they are at group level? Is that on document level or worksheet or object?

What I mean with the tag object is that you can temporally select values for your analyze.

Tags are annotations that can be attached to marked rows. Each row can only contain a single tag from each tag collection, but the document can contain many tag collections simultaneously. A tag collection is basically a column containing a set of different tags, or annotations. Each tag collection is represented by a new column in the data table and can be used for filtering the data, just like any other column. Tags can only be attached to rows from a single data table, but the same tag collection and tag names can be used for multiple data tables.

Tags are managed in the tag panel, which can be displayed by clicking on the Tag Panel button on the toolbar, , or by selecting View > Tag Panel. The visibility of the tag panel is controlled per page. The panel visibility of new pages is inherited from the active page.





Regards,

Wouter

johnw
Champion III
Champion III

When the calculated columns are at a group level, I will usually calculate them as expressions in the objects. So if I have a chart when I want to display a monthly total of sales, I would add a dimension of Month and an expression of sum(Sales). Then if I decide to add Customer as another dimension, I'll get my totals by month and customer without any script changes. I could also use a pivot table to have my total for each customer within a month, and then a total for each month, or a total for each month within a customer, and then a total for each customer. All of that could be done by just moving dimensions around rather than by changing the script.

Something like Profit, though, I might well calculate in the script, as it could be calculated at the lowest level of detail. So my script might include "Revenue - Cost as Profit," so that I could make my expressions just say sum(Profit) instead of sum(Revenue - Cost).

In regards to the tags, it sounds like an example would be for a user to go in and tag the customers and regions of the country that they most commonly analyze. They could use multiple tag collections to create different sets of data to analyze, and then select which set to use for analysis. Is that sort of what you're talking about? If so, that particular example sounds like bookmarks.

Or would an example be that we're looking at a bunch of orders, and the user is going in and typing notes on each order, like "customer over credit limit" or "verify that material is being produced quickly enough". And then they could just select a value in this column to, say, find and filter down to all of the cutomers that they had tagged as being over their credit limit? You could sort of do that with bookmarks, but that might lend itself better to... oh, what are they called. There's a way to add fields to a table that are enterable, which might work here. I haven't used it in any real applications.

Not applicable
Author

Thanks for your help so far. I understand the calculation definition and I will examine the bookmarks functionality.

Regards,

Wouter

Not applicable
Author

Hey John do you have an example that you can pass me of the using of adding calculate column in a pivot table chart pls??

Regads

Carlos

johnw
Champion III
Champion III

I'm not sure what you mean by a "calculate column". Do you mean a calculated dimension? If so, here's a bad example. The data has months, but I want to break it out by the first half of the year and second half of the year instead of by months. I can do this with a calculated dimension in the table. I shouldn't do it that way, which is why it's a bad example, but I could. The right way to handle this particular example would be to calculate the half of the year in the script. Also, as best I can tell, calculated dimensions are VERY slow.