Expression

[smilingjohn]

Hi all ,

can please soemoen correct my belwo expression ?

Iam trying to get the 2 years back data with the below expression ...

Please rectyfy

Count({<[Dep]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate),-2))<=$(=YearEnd(LoadDate),-2))"}>}ID)

Thanks in advance

[jonathandienst]

I assume Jira_LoadDate is a field. This will not be evaluated properly in the set expression. You probably need some sort of aggregation (Min or Max):

Count({<[Dep]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate),-2))<=$(=YearEnd(Max(Jira_LoadDate)),-2))"}>}ID)

[jyothish8807]

Hi John,

Try like this:

Create two variables:

1. vMaxYear-2 : = date(max(LoadDate),'YYYY') - 2

2. vMaxYear : =date(max(Jira_LoadDate),'YYYY')

Make sure the Purchase Date is also in 'YYYY' format. Also check the output of the variable once, since i do not know the formatas you actually have these dates.

Count({<[Dep]={'A'},PurchasedDate={">=$(vMaxYear-2) <=$(vMaxYear )"}>}ID)

Br,

KC

[its_anandrjs]

You can try this as well by Addmonths by take expression to 2 years back.

=Count({<[Dep]={'A'},PurchasedDate={">=$(=AddMonths(Max(LoadDate),-24))  <=$(=AddMonths( Max(Jira_LoadDate)),-24))"}>}ID)

[smilingjohn]

Hi Jonathan

sorry..

the expression is like this

Count({<[Dep]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate),-2))<=$(=YearEnd(LoadDate),-2))"}>}ID)

the field is Loadate ...

by mistake i type Jira_loaddate

[smilingjohn]

Hi ANand sorry the expression is like this

Count({<[Dep]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate),-2))<=$(=YearEnd(LoadDate),-2))"}>}ID)

the field is Loadate ...

by mistake i type Jira_loaddate

[PrashantSangle]

did you try jonathan suggestion

you must max(LoadDate)

Regards

[its_anandrjs]

What O/P you get

May be this are you have to try

=Count({<[Dep]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate),-2))<=$(=YearEnd( Max(LoadDate),-2)))"}>}ID)

Or

=Count({<[Dep]={'A'},PurchasedDate={">=$(=Yearstart( Addmonths(Max(LoadDate),-24))) <=$(=YearEnd( Addmonths( Max(LoadDate),-24)) ))"}>}ID)

[sasiparupudi1]

Count({<[Dep]={'A'},PurchasedDate={">=$(=YearStart(Max(LoadDate),-2))<=$(=YearEnd(Max(LoadDate))))"}>}ID)

Make sure that the format of the PurchasedDate and LoadDate is the same

[jonathandienst]

>>Hi Jonathan

sorry..

the expression is like this ....

Just change the field name and leave the rest of the expression.