Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Hi,
I think u should use the date formate like
Aggr(Max({$<DATE1={"<=$(date(T1,'DD/MM/YYYY'')"}>) VALUE)
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.
Someone can give me some suggestion on it?
Many thanks
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.
OK, now everything is fine.
Here the syntax:
only(if(DATE1=Aggr(NODISTINCT max({$<DATE1={'<=$(=$(myDate))'},VALUE={CONS,REC}>} DATE1),CODE),VALUE))