topic Expressions using same dimension for different field lookup in QlikView

Expressions using same dimension for different field lookup

Wed, 20 Apr 2011 14:06:56 GMT

in same graph,

I want to show 3 expressions, but 2 referencing same date dimension

i.e.

in expression 1, I want to count records, where date dimension is "Start Date" - which must be cumulative too

in expression 2, I want to count records, where date dimension is "Expiry Date" - which must be cumulative too

then 3rd expression then I am simply getting difference

now obviously I can do the variance and cumulative, but need assistance with how to reference dates using same dimension to link to 2 seperate expressions and fields, would this be set analysis or something more sinister?

SV:Expressions using same dimension for different field lookup

ToniKautto — Wed, 20 Apr 2011 15:27:48 GMT

Could you please provide a sample QVW to show more in detail what data you have and what you are trying to accomplish?

If the data is of sensitive nature please scramble the sensitve fields by;
Settings > Document Properties > Scrambling

Expressions using same dimension for different field lookup

Thu, 21 Apr 2011 08:52:08 GMT

here is example

SV:Expressions using same dimension for different field lookup

Tue, 26 Apr 2011 10:20:32 GMT

Hi Toni,

any further answers for me?

I need to finalise this QV model, and only thing outstanding is this.

I have single dimension that need to be referenced from 2 seperate expressions, using the same dimension dates.

I.e. for 1 July 2010. I have 1 expression looking this up against count of items using start date. and another using the same date, but counting items against Expiry date...

SV:Re: SV:Expressions using same dimension for different field lookup

ToniKautto — Thu, 28 Apr 2011 14:54:00 GMT

As you can see in the Table view, press Ctrl + T in the desktop client, there is no relation between your tables. This means that a selection in either table will not affect the selection in the other table, hence there is no relation between the tables.

My suggestion is that you do not makes the MonthYear contatenation in the dimension field, but instead add this as a field in your calender. Then you add the MonthYear also in the data table, which will then be your key to link the tables together. The field will then also be suitable to use as your dimension value.

SV:Re: SV:Expressions using same dimension for different field lookup

Thu, 28 Apr 2011 15:15:07 GMT

Thanks Toni,

even if I do this, how do I relate start date and expiry date to this single dimension on the same chart?

i.e. for one it must count where new MonthYear is referenced in Start Date

and next where MonthYear is referenced in Expiry date??

Expressions using same dimension for different field lookup

Thu, 28 Apr 2011 15:23:47 GMT

see updated example

by right the graph should only show 3 items in total...

SV:Re: SV:Re: SV:Expressions using same dimension for different field lookup

ToniKautto — Fri, 29 Apr 2011 11:30:37 GMT

I can not see any major updates in the attached file, your tables are still not linked.

Linking both start and end date to one dimension is a bit unlogical to me in this case, since your start and dates are not always within the same month. What is the purpose of such a chart?

SV:Re: SV:Re: SV:Expressions using same dimension for different field lookup

Fri, 29 Apr 2011 11:48:45 GMT

Hi Toni,

thansk for feedback

the rationale behind both on same chart is this:

I need to show how many new products per month and then how many expired products per month, with nett result per month showing the growth

i.e. if June 2010 had 4 new items, but expired 2, using start date and expiry date seperately, then I want to show for June 2010 the nett result or variance was 2

now by linking both dates to same dimension, will give me that, but uniqueness per table is breached, by linking calendar to both expiry date and start date in same table...

I though of iff statements with countif ir sumif? maybe the answer?

with something like sum(if(start date<=cal date,1,0)??? and same for expired items too, in its expression showing sum(if(expiry date<= date,1,0))???

but the expressions do not return what I want, using wrong dimensions, etc maybe???

or I have to use pivot instead of straight table, linking both start date and expiry date to rows and calendar dimension to columns maybe???

in my example the items I needed should look like this...:

Date Accum Created Accum Expired Accum Nett

Jan 2002 2 0 2

Mar 2004 2 1 1

May 2009 3 2 1

Mar 2010 3 3 0