Set Analysis and IF Statement

[dthornburg]

Could anyone help me convert this to a Set Analysis expression? This works fine until a year is selected and then it breaks. I always want to sum the invoice_line_extension for the prior year no matter what year is selected.

Works Fine - Sum (if (InYearToDate (invoice_date, Today(), -1), invoice_line_extension))

Does not work - Sum (if ({<=InYearToDate>=} (invoice_date, Today(), -1), invoice_line_extension))

Thanks.

[sushil353]

hi,

First of all calculate the previous year from your date field

vLastYear=Year(Max(invoice_date))-1

also get the year in your data model say you have Year as a field..

then your expression would be

sum({<Year={''$(vLastYear)''}>} invoice_line_extension)

HTH

Sushil

[CELAMBARASAN]

Try with this

Sum({<invoice_date={">=$(=YearStart(Today()))<=$(=Date(Today() -1))"}>} invoice_line_extension)

[dthornburg]

Hi,

Neither one of these worked. When I try an use the first suggestion I get a null value. The 2nd gives me the total for the previous year. The If statement that works without suggestions provides last years total to date not for the entire year.

[#5]

Hello,

Maybe this :

Sum({<invoice_date={">=$(=YearStart(Today(), -1))<=$(=Date(AddMonths(Today(),-12) ))"}>} invoice_line_extension)

[Gysbert_Wassenaar]

Try: sum({<invoice_date={"=year(invoice_date)=year(today())"}>} invoice_line_extension)