Compare current sum Rev VS last year sum Rev

[wassimharfoush]

I am trying to compare the sum of Revenue for period based on current selection of a DateField VS The same period for

previous year and I am using the expression for previous year period:

Sum({<Datefield={'$(=addyears(DateField-1))'}>}Rev)

That works when the user select a single period but doesn't work when the user select multi period.

[Anonymous]

Sum({<Datefield={" >= $(=addyears(Min(DateField), -1)) <= $(=addyears(Max(DateField), -1))"}>}Rev)

[Anonymous]

Try this expression:

Sum({$+<[Fiscal Year]={"$(=AddYears([Fiscal Year],0)-1)","$(=AddYears(Min([Fiscal Year]),0)-1)"}>}Rev)

If you don't want to show them without a selection then you need to active the conditional expression check box and add this expression

GetSelectedCount([Fiscal Year])>0

Regards

[alec1982]

None of the above work.. Any other suggestions?

Best,

Alec

[alec1982]

Hi Waild,

the fiscal year field that you use, is it a year column? or full date? The solution doesn't work on my end.

Best,

Alec

[Anonymous]

This one should work

Sum({$<DateField={">= $(=addyears(Min(DateField),0) -1) <= $(=addyears(Max(DateField),0) -1)"}>}Rev)

[alec1982]

Thank you for your help but still not working..

[Anonymous]

It is a year field. You might need to use Makedate function before addyears if you apply it on a full date. So try it with a year column

[alec1982]

I also tried the following with no luck:

=Sum({$<RDATEValue={">= $(=addmonths(Min(RDATEValue),0) -12)"," <= $(=addmonths(Max(RDATEValue),0) -12)"}>}REV)

it is not returning error but it returns 0 which means the expression is correct but not sure why it returns 0.

Any help is appreciated

[Anonymous]

Is RDATEValue a month column?