8 Replies Latest reply: Mar 21, 2012 9:53 AM by Tiji Mathew RSS

    Calculated metrics or columns

      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

          Year(today() )- Year(DOB)

           

          This should produce the age.

            • Re: Calculated metrics or columns

              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

                  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

                      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
                          Erich Shiino

                          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

                              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
                                  Erich Shiino

                                  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

                                      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