topic “Below”, “above” and set analysis in QlikView

“Below”, “above” and set analysis

Mon, 28 Nov 2011 08:24:07 GMT

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,

Re: “Below”, “above” and set analysis

swuehl — Mon, 28 Nov 2011 10:48:15 GMT

Tomer,

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

Hope this helps,

Stefan

Re: “Below”, “above” and set analysis

Mon, 28 Nov 2011 12:41:44 GMT

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?

“Below”, “above” and set analysis

SunilChauhan — Mon, 28 Nov 2011 12:43:39 GMT

you can try this

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

hope this help

“Below”, “above” and set analysis

swuehl — Mon, 28 Nov 2011 13:09:12 GMT

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

“Below”, “above” and set analysis

Mon, 28 Nov 2011 13:28:01 GMT

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.

“Below”, “above” and set analysis

swuehl — Mon, 28 Nov 2011 17:11:36 GMT

Tomer,

I think you could either use

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

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

Regards,

Stefan

“Below”, “above” and set analysis

Tue, 29 Nov 2011 06:42:43 GMT

hanks a million for that.

Tomer