Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Maybe like
=SUM(
Aggr(
If( [JobLedgerDate] >= Max(TOTAL<Proyecto> {<A_Date = {'<=$(vEndDate)'}>} A_Date) and JobLedgerDate <= '$(vEndDate)', [Hours])
,Proyecto,JobLedgerDate)
)
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.
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.
Maybe like
=SUM(
Aggr(
If( [JobLedgerDate] >= Max(TOTAL<Proyecto> {<A_Date = {'<=$(vEndDate)'}>} A_Date) and JobLedgerDate <= '$(vEndDate)', [Hours])
,Proyecto,JobLedgerDate)
)
Thanks, It works!