# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

## Use Set Analysis with DATE<=selected date

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

Tags (3)
1 Solution

Accepted Solutions
Not applicable

## Use Set Analysis with DATE<=selected date

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.

5 Replies
Contributor III

## Use Set Analysis with DATE<=selected date

Hi,

I think u should use the date formate like

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

Not applicable

## Use Set Analysis with DATE<=selected date

`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)`

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

Not applicable

## Use Set Analysis with DATE<=selected date

Someone can give me some suggestion on it?

Many thanks

Not applicable

## Use Set Analysis with DATE<=selected date

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.

Not applicable

## Use Set Analysis with DATE<=selected date

OK, now everything is fine.

Here the syntax:

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