topic Calculated dimensions in QlikView

Calculated dimensions

Fri, 19 Mar 2010 09:16:28 GMT

The concept of calculated dimensions is not quit clear to me. I have 2 issues with dimensions that I can't solve :

- Is it possible to add a calculated dimension that contains values that are not selected

e.g.: You select 1 year 1 month in your application and you want to view a bar chart with 6 months until the selected month . The image below is what we need to achieve if 2010-03 is selected

- Is it possible to only show dimension members with sales greater then a given treshhold ( cf "having sum(sales) > number in SQL)

e.g.: In the image above we only wish to see 201001 and 201002 which are greater then 2.500.000

Can some help pls.

Calculated dimensions

Miguel_Angel_Baeyens — Fri, 19 Mar 2010 10:21:51 GMT

Hello Johann,

You can select all values not selected in one dimension by right clicking on the listbox and select excluded values, or check in the properties of the listbox "Include Excluded Values in Search"

I'd use a different expression with set analysis to show those values only, a very simple example of that would be

Sum({< Sales = {">2500000"} >} Sales)

. If you want to show not a listbox but a chart with all values that do not accomplish some of your criteria you can do something like

Sum({< Year -= {2010} >} Sales)

which will exclude all records with 2010 as value in Year field.

You can create as well a "flag field" in your script given that threshold, but I think you want that parameter to be variable, so this idea won't be very effective.

Simplifying, a calculated dimension has to do with operations with fields (not aggregations) such as

Year(Date)

as Year dimension (if you don't have any), or

left(CompanyCode, 3)

as Initials or County Code... The more you complicate this dimensions, the longer your performance will take. It's better to pass all the work on to expressions, and using set analysis when possible, combined with the load script.

Regards.

Calculated dimensions

Fri, 19 Mar 2010 11:27:58 GMT

This is not what I ment.

- I only want to select 1 month (eg. 2010-03) in a list box and the chart should show the 6 months before 2010-03 (2009-10,2009-11,2009-12,2010-01,2010-02,2010-03). This means the content of the dimension of the graph should be dynamic.

- The "having"-SQLstatement that I wish to achieve is a bit more complex: I am trying to create a table with only the dates where there is a difference between ticket sales and ticket cash. In SQL it would look like : select date,sum(sales)-sum(cash) from tickets having sum(sales)-sum(cash) <> 0.

In qlikview I created a table with the dimension date and the expression sum(sales)-sum(cash) which resulted in the image below. I now only want to see the dates where "Difference" <> 0 (meaning 05/01, 07/01, 09/01 and 10/01)

Greets

Calculated dimensions

michael_anthony — Fri, 19 Mar 2010 11:32:21 GMT

Johan,

Not sure that Calculated Dimension is what you need. Rather modifying your expression should do what you want and leave Month as the only dimension.

For your first issue - assuming you can't have the user simply select 6 months - extending the date range back 6 months from the selected month you can use Set Analysis. Check Help doco for more details.

Something like Sum({$<Month = {">$(vLess6Months)<=$(vMaxMonth)"} >} Sales)

vLess6Months and vMaxMonth are variables. I sometimes have issue with date representation so make sure the variables calculate same format as Month, eg.

vLess6Months = =Date(MonthStart(Min(Month),-6),'YYYYMM') , vMaxMonth = Date(MonthEnd(Max(Month)),'YYYYMM')

Assumes you hold Month as YYYYMM in your file.

For second issue, various ways, one would be extending your expression to be.

If(Sum({$<Month = {">$(vLess6Months)<=$(vMaxMonth)"} >} Sales) > 250000,

Sum({$<Month = {">$(vLess6Months)<=$(vMaxMonth)"} >} Sales),Null())

This will return Null for the months < 2.5m.

Calculated dimensions

Miguel_Angel_Baeyens — Fri, 19 Mar 2010 12:43:50 GMT

Hello Johann,

Now I get your point. Michael's approach is good enough.

Regards.

Calculated dimensions

Fri, 19 Mar 2010 14:42:55 GMT

I've found a solution for problem number 2:

if(sum(sales)-sum(cash)<>0,Null(),sum(sales)-sum(cash))

Problem number 1 still remains unsolved. I don't even know if it is possible.

Calculated dimensions

michael_anthony — Sat, 20 Mar 2010 02:11:23 GMT

Set Analysis will solve problem 1. Can apply same logic in each Sum function you have listed.

Use Sum({$<Month = {">$(vLess6Months)<=$(vMaxMonth)"} >} Sales)

vLess6Months and vMaxMonth are variables. I sometimes have issue with date representation so make sure the variables calculate same format as Month, eg.

vLess6Months = =Date(MonthStart(Min(Month),-6),'YYYYMM') , vMaxMonth = Date(MonthEnd(Max(Month)),'YYYYMM')

Calculated dimensions

Wed, 02 Mar 2011 09:22:24 GMT

Hi,

I have a similar requirement.

The only thing is the Date selection is also dynamic.

How can I get the selected date in the SET analysis instead of vLess6Months.

Regards,

Chinmay