“Below”, “above” and set analysis

[#1]

After I try, very hard, I promises you… now it’s time to askyour help…

I have few calculated values that I like to display in the samerow.

I have date, name, age1 age2 age3 age6 and age9

At date 02.11.11 – age1 value is 6, age27

At date 01.11.11 – age1 value is 3,age 2 is 6

What I like to see is that in the row from  01.11.11 my columns will be age1-3 and age2-7

I try to use the “below” and “above”, but there are casesthat some dates are missing, therefor using “above” or “below” will not givethe right flow.

I enclosed a short excel file with example of the date andwhat I like the result to be.

Thanks in advance,

T.

[swuehl]

Tomer,

maybe like attached? I use an additional data island as date dimension, then just check the different date stamps.

Hope this helps,

Stefan

[#3]

Thanks.

I try to use what you sent me ,but I have a problem withthis:

only(if( XDate=date,[Age 9]))

my ‘age 9’ is calculated number and I think I can’t replacethe “age 9” with the calculation I made,

Could be that the “only” is not allowing it?

T.

[SunilChauhan]

you can try this

Below(if( XDate=date,[Age 9]))

hope this help

[swuehl]

Are you using an aggregation function to calculate Age9?

If so, how does your expression looks like?

Since only() is also an aggregation function, it is not allowed to embed an aggregation function inside only(). But you can use advanced aggregation to get around this, maybe like

=only( aggr( if(XDate=date,YOUREXPRESSION), date))

I noticed that your date may have missing values (e.g. 2011-11-16), so I think chart inter record functions like below(), above() etc. are to be used with caution here.

Hope this helps,

Stefan

[#6]

You are right,above() below() etc. will not be helpful in this case.

My agecalculation is “weighted average”

sum(avg_v9 * cnt_age9)/sum(cnt_age9))

this is what I’m using.

I try to you what you suggested, but I did not get any result.

Thanks.

T

[swuehl · Accepted solution]

Tomer,

I think you could either use

= only(aggr( if(XDate=date, sum(avg_v9 * cnt_age9)/sum(cnt_age9)) ), XDate, date))

or

= only( if(XDate=date,aggr( sum(avg_v9 * cnt_age9)/sum(cnt_age9)) ,date)))

Regards,

Stefan

[#8]

hanks a million for that.

Tomer