Hello all,
I have a table with departments and month back to sum. like this:
| Department | FromMonth | ToMonth |
|---|
| A | 12 | 1 |
| B | 12 | 6 |
| C | 12 | 3 |
for example: if we are in Aug.16 we need to take in
Department A sum of amount Aug.15 - July. 16 >=AddMonths(Date,-1) <=AddMonths(Date,-12)
Department B sum of amount Jan.15 - Feb. 16 >=AddMonths(Date,-6) <=AddMonths(Date,-12)
Department A sum of amount June.15 - May. 16 >=AddMonths(Date,-3) <=AddMonths(Date,-12)
I put the dates inside variables:
vDateFrom=AddMonths(if(GetSelectedCount(MonthName)=1,MonthName,Max(MonthName)),-FromMonth)
vDateTo=AddMonths(if(GetSelectedCount(MonthName)=1,MonthName,Max(MonthName)),-ToMonth)
in my set analysis this is my expression:
sum({<Date={">=$(=$(vFromDate))<=$(=$(vToDate))"},MonthName=>} Amount)
the expression working fine when i chose only one department. when more then one possible vToMonth is null.
my final table need to be:
| Department | MonthName | Sum |
|---|
| A | Aug. 16 | sum({<Date={">=01/08/2015<=01/07/2016"},MonthName=>} Amount) |
| A | July 16 | sum({<Date={">=01/07/2015<=01/06/2016"},MonthName=>} Amount) |
| A | June 16 | sum({<Date={">=01/06/2015<=01/05/2016"},MonthName=>} Amount) |
| B | Aug. 16 | sum({<Date={">=01/01/2015<=01/02/2016"},MonthName=>} Amount) |
| B | July 16 | sum({<Date={">=01/12/2014<=01/01/2016"},MonthName=>} Amount) |
| C | Aug. 16 | sum({<Date={">=01/06/2015<=01/05/2016"},MonthName=>} Amount) |
how can i achieved that?
BR
Ariel
