Expressions using same dimension for different field lookup

[#1]

Hi

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?

[ToniKautto]

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

[#3]

here is example

[#4]

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...

[ToniKautto]

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.

[#6]

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??

[#7]

see updated example

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

[ToniKautto]

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?

[#9]

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