topic Use Set Analysis with DATE<=selected date in QlikView

Use Set Analysis with DATE<=selected date

Fri, 25 Feb 2011 10:57:05 GMT

Dear All,

I'm trying to escape from following situation: i need to obtain a Straight Table with one dimension=CODE, second dimension= maxDATE1 for each code calculated not to absolute max(DATA1) value but with constraints max(DATE1)<=DATE2.

I use this function: Aggr(Max({$<DATE1={"<=$(T1)"}>) VALUE)

My expected result is ==> A 25/10/2009 z 26/10/2009

I would group on VALUE for each different CODE selecting NOT max(DATE1) but max(DATE1)<=DATE2, i'm not able to write down this expression.

Here an example;

CODE - DATE1 - VALUE - DATE2

A 22/10/2009 x 26/10/2009

A 23/10/2009 x 26/10/2009

A 25/10/2009 z 26/10/2009

A 28/10/2009 z 26/10/2009

B ......

C ......

Many thanks

Use Set Analysis with DATE<=selected date

maneshkhottcpl — Fri, 25 Feb 2011 11:02:10 GMT

Hi,

I think u should use the date formate like

Aggr(Max({$<DATE1={"<=$(date(T1,'DD/MM/YYYY'')"}>) VALUE)

Use Set Analysis with DATE<=selected date

Fri, 25 Feb 2011 11:33:40 GMT


Manesh wrote:
Hi,
I think u should use the date formate like
<blockquote><pre>Aggr(Max({tiny_mce_markerlt;DATE1={"<=$(date(T1,'DD/MM/YYYY'')"}>) VALUE)

Thanks for your reply Manesh, i try with:

=Aggr(Max({$<date(DATE1)={"<=$date('$(T1)','DD/MM/YYYY')>"}>}) VALUE)

where T1 is a variable with limit date., but i'm still obtaining an Error in calculated dimension.

Use Set Analysis with DATE<=selected date

Mon, 28 Feb 2011 00:24:01 GMT

Someone can give me some suggestion on it?

Many thanks

Use Set Analysis with DATE<=selected date

Mon, 28 Feb 2011 05:59:52 GMT

I've found the solution:

Dimension = CODE

Expression1:

=Aggr(Max({$<DATE1={"<=28/11/2010"},VALUE={CONS,REC}>}DATE1), CODE)

Expression2:

only(if(DATE1=Aggr(NODISTINCT max({$<DATE1={"<=28/11/2010"},VALUE={CONS,REC}>} DATE1),CODE),VALUE))

Now the last question :-), i need to get DATE2 from a variable, instead "<=28/11/2010", i've tried {<=$(#myDate)}, but it doesnt'work.

Use Set Analysis with DATE<=selected date

Mon, 28 Feb 2011 12:58:10 GMT

OK, now everything is fine.

Here the syntax:

only(if(DATE1=Aggr(NODISTINCT max({$<DATE1={'<=$(=$(myDate))'},VALUE={CONS,REC}>} DATE1),CODE),VALUE))