topic Re: Calculated metrics or columns in QlikView

Calculated metrics or columns

Tue, 20 Mar 2012 21:43:50 GMT

Is the script editor the only place that will allow the creation of aggregate variables?

I was trying to pull data of birth and calculate age within qlikview? Is there a way without going to the script editor to calculate Age values in the sheet.

Regards

Tiji

Re: Calculated metrics or columns

Tue, 20 Mar 2012 21:50:08 GMT

Year(today() )- Year(DOB)

This should produce the age.

Re: Calculated metrics or columns

Tue, 20 Mar 2012 21:56:21 GMT

but where do I place this expression? in the script editor?

I think I am asking "Where do I create calculated fields other than the script editor for loading?"

Thanks

Tiji

Re: Calculated metrics or columns

Tue, 20 Mar 2012 21:57:38 GMT

You can put it in the script, or you can use it in a single object, or you can place it in a variable

Calculated metrics or columns

Tue, 20 Mar 2012 22:05:43 GMT

In Expression overview, I don't see an add option to get the object added into the list of fields. How do I add the single object or the variable editor.

Can you tell me the menu option to get to the new field editor screen?

Regards

Tiji

Calculated metrics or columns

erichshiino — Tue, 20 Mar 2012 22:16:54 GMT

Hi,

Instead of the Year (A) - year(B), you can use the age() function.

age(timestamp, date_of_birth)

Returns the age at the time of timestamp (in completed years) of somebody born on date_of_birth.

The best approach ( if it makes sense in your case) is to put this in your load script.

Let us say that you script is similar to this:

Load user_id,

dateofBirth,

from / resident

You can create a new field as you would do in SQL:

Load user_id,

dateofBirth,

age ( today(), dateofBirth) as Age

from / resident

Then, you would be able to use the new field 'Age' in your interface.

Hope this helps,

Erich

Calculated metrics or columns

Tue, 20 Mar 2012 22:23:04 GMT

Making the change to the load script by adding this computed column is the only way to add the field to the data set.

I was hoping to hear that I missed a menu option that would help me create the field tied to a data set.

From business user perspective, to ensure adoption, I will have to give them a file or field in the database that has the age calculated in the query to pull the data.

With all this coolness in the tool, I just wish the script editor would not come in the way of adding custom fields to the data set. I understand that it could be the design for the tool is addressing a different use case here.

Thank you for your attention to my silly question and providing a detailed answer.

Calculated metrics or columns

erichshiino — Tue, 20 Mar 2012 22:36:03 GMT

I'm glad I could help!

There are no silly questions. It's important to check any doubts.

Another alternative would be the creation of calculated dimension. You can do that directly on the chart.

Chart properties -> Dimension -> Add calculated dimension.

The syntax would be almost the same:

=age ( today(), dateofBirth)

Since it needs to be calculated again after any selection, you may have performance problems, specially for date calculations as mentioned here:

http://community.qlik.com/docs/DOC-2715

Best Regards,

Erich

Calculated metrics or columns

Wed, 21 Mar 2012 13:53:37 GMT

Thank you for your response and the link as well.

I see the calculated dimension option for chart.

Basically by design, Qlikview should be used for data discovery and not ETL or aggregation. The aggregation happens for the chart by the dimensions but the fields needed for the analysis would be most useful if the data is already available as a field at the detail level. This will help qlikview be utilized to its full potential rather than make it do what it is not designed to do.

My pain point with Qlikview is still the clunky script interface to create new fields. I wish there was an add button in the expression overivew window to add new fields. But I understand the purpose of limiting such a feature as well.

Thank you Erich!

Thanks & Regards

Tiji