Problem with nested set analysis

[#1]

Hi, I have the next expression:

=SUM({<[JobLedgerDate] = {">=$(=Max({<A_Date = {'<=$(vEndDate)'}>} A_Date))<=$(vEndDate)"}>}[P_Hours])

when I set a vEndDate like 10/31/2014 I have this:

2,50 hours for P12/0044, but if I filter this Proyecto:

I have 16,5 hours which is the correct value. Why it doesn't show correct value when I have chart without filters?

Regards.

[swuehl]

Are you looking for a max(..) evaluation per dimension line?

$(=Max({<A_Date = {'<=$(vEndDate)'}>} A_Date))

This won't work, since the dollar sign expansion is evaluated only once per chart.

[#3]

Ok, and how could I get this result?

I have a variable with a date, for exemple 31 of July of 2014, and I want to sum hours in a table "Proyectos" with dates between max date in a table "Avances" smaller than 31 of July of 2014 and this date.

For example:

Proyectos:

LOAD * Inline [

     Proyecto,JobLedgerDate,Hours

     P12/0044,05/03/2014,5

     P12/0044,22/06/2014,7

     P12/0044,14/08/2014,3

     P12/0044,03/10/2014,4

     P12/0044/06/11/2014,6

];

Avances:

LOAD * Inline [

     Proyecto,A_Date,Percent

     P12/0044,31/05/2014,35

     P12/0044,30/09/2014,60

];

I'm working with DD/MM/YYYY date format. If my variable has the value 31/07/2014, I want the greater value in table Avences smaller than 31/07/2014, in this case, 31/05/2014, and sum Hours in Proyectos with JobLedgerDate between 31/05/2014 and 31/07/2014, in this case 7 hours because I only have a row with 22/06/2014 date.

Another example: If my variable was 31/12/2014 I want 10 hours as result, because greater date in Avances smaller than 31/12/2014 is 30/09/2014, and I have 2 rows between this dates with 4 and 6 hours.

How could I get this?

Thanks in advance.

[swuehl · Accepted solution]

Maybe like

=SUM(

Aggr(

If( [JobLedgerDate] >= Max(TOTAL<Proyecto> {<A_Date = {'<=$(vEndDate)'}>} A_Date) and JobLedgerDate <= '$(vEndDate)', [Hours])

,Proyecto,JobLedgerDate)

)

[#5]

Thanks, It works!