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

[sunny_talwar]

Try one of these

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

or this

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

Make sure to use the correct format of your PurchasedDate within the set modifier.

Also, read here:

Dates in Set Analysis

[smilingjohn]

Hi sunny

Iam using the below the below expression ..

but somewhere i feel This is not correct ..PLease correct me

Pick(Dim,

count({<[Department]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate)))<=$(=Max(LoadDate))"}>} Key),

Count({<[Department]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate),-2))<=$(=YearEnd(Today(),-2))"}>} Key)/12,

Count({<[Department]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate),-1))<=$(=YearEnd(Today(),-1))"}>} Key)/12,

count({<[Department]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate)))<= $(=Max(LoadDate))" }>}Key)/12))

[smilingjohn]

In dimension iam using this ...

=Pick(Dim,MonthName(Created),$(vloadyear)-2,$(vloadyear)-1,$(vloadyear))

vloadyear= year(LoadDate)

I willl be using the loadDate as a filter ..

When i select any date form load date the data should display accordingly....

if i select 2016 from loadate it should go back and show avgof 2014 and avg 0f 2015,2016

as shown in the screenshot

[sunny_talwar]

I guess I understand what you want, but where is the problem? Which part of the expression not working or the whole expression not working?

[smilingjohn]

Hi sunny .

Thanks ..

This expression works fine ...the only doubt I have to confirm from you for the below expression is that ....if you look iam using

Yearend(Today() .....will this work fine ....even in the future for 2018 ...

Pick(Dim,

count({<[Department]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate)))<=$(=Max(LoadDate))"}>} Key),

Count({<[Department]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate),-2))<=$(=YearEnd(Today(),-2))"}>} Key)/12,

Count({<[Department]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate),-1))<=$(=YearEnd(Today(),-1))"}>} Key)/12,

count({<[Department]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate)))<= $(=Max(LoadDate))" }>}Key)/12))

[sunny_talwar]

I think it should work... I don't see why it wouldn't

[greend21]

Maybe try an If statement in the load that will give you all dates you want as a 1. The below should give you dates between two years and ago the end of the current Load Date month:

If(yearstart(AddYears(today(),-2))<=LoadDate and (MonthEnd(today())>=LoadDate,1,0) as YearFlag

Then use YearFlag in your set analysis.

Count({$<YearFlag={1}, Dep={'A'}>}ID)