Calculated Dimension

Fri, 21 Nov 2014 17:39:38 GMT

Hello Friends

I have a dimension in my Pivot table called Account

It has account numbers as values that start with 1, 2, 3, 4...8

I want to filter it in such a way that it shows only account numbers that begin with 4 in my pivot chart.

Please tell me what is the best way to do it.

Thanks

Rizwan

Re: Calculated Dimension

agomes1971 — Fri, 21 Nov 2014 17:54:42 GMT

Hi,

you use set analysis:

Sum({$<account=P({1<={"$(=4)*"}>}account)>}Sales)

Hope it helps.

André Gomes

Re: Calculated Dimension

Fri, 21 Nov 2014 17:56:41 GMT

Hello Andre

What is Sales in here? Is there any other way than Set Analysis. I am

trying to just write a Calculated Dimension.

Thanks

On Fri, Nov 21, 2014 at 9:55 AM, Andre Gomes <qcwebmaster@qlikview.com>

Re: Calculated Dimension

JonnyPoole — Fri, 21 Nov 2014 18:01:48 GMT

You can generally do this in the calcualted dimension expression or in the chart expressions with set analysis or if statements.

If you need to do it in a calcualted dimension, below is an example using if(). Note that you need to suppress the NULLs which will catch all the accounts that don't begin with 4.

Re: Calculated Dimension

MarcoWedel — Sat, 22 Nov 2014 00:18:27 GMT

Hi,

if there are more than one expressions, it might be easier to just create one calculated dimension, e.g. like this:

hope this helps

regards

Marco

Re: Calculated Dimension

Sat, 22 Nov 2014 00:20:56 GMT

HI syed

hope you help this

To make a chart in QlikView – in any Business Intelligence tool, for that matter – you need to have one or several dimensions; entities with discrete values that you use as grouping symbols. But where should you define these dimensions: In the script or in the object where the dimension is used?

In most cases, you will use an existing field as dimension, i.e. an attribute that exists in the source data. In such a case, the answer to the above question is easy: Just make sure to load the field in the script, and you're done.

But in some cases you want to use derived attributes: Attributes that do not exist in the source data but one way or another can be calculated from existing fields.

One example is the fields of the Master Calendar: Year, Month, etc. These can all be derived from a date found in the source data:

Month(Date) as Month

Year(Date) as Year

A more advanced example is if you want to classify or rank a field. The following expression returns ‘A’ for the 10 best customers and a ‘B’ for the rest:

If(Aggr(Rank(Sum(Sales)),Customer)<=10,'A','B')

For such fields the above question is very relevant: Should they be calculated in the script and saved as fields, or should they be calculated on the fly in a sheet object?

There are pro:s and con:s with both approaches: A field calculated in the script is calculated once and for all, so it does not need to be re-calculated every time the user clicks. Hence, response times will be slightly shorter if the field is calculated in the script.

On the other hand, in some cases you want the field to be re-calculated every time the user clicks. A good example is the classification using Rank() above. Most likely you want this field to depend on the selection made: If you have selected a product, you want to see the classification of the customers given this selection. Such a number is in its nature dynamic and should be calculated every time the user clicks.

The key is whether the calculated field should be static or dynamic. The field Month is static: A specific date always belongs to the same month, irrespective of user selection. As opposed to a classification or a rank where the calculation usually should be dynamic, since the result potentially could change every time the user clicks.

Bottom line is that dynamic fields must be calculated in the chart or the list box. But for static fields it is better if they are calculated in the script, since precious CPU-time otherwise will be unnecessarily spent every time the user clicks.

HIC

Note: The Rank() function cannot be used in the script, so if you want to calculate a static rank in the script, you need to do it in a different way, using e.g. a combination of Order By and RecNo().

topic Re: Calculated Dimension in QlikView

Calculated Dimension

Re: Calculated Dimension

Re: Calculated Dimension

Re: Calculated Dimension

Re: Calculated Dimension

Re: Calculated Dimension