Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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:
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))
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
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?
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))
I think it should work... I don't see why it wouldn't
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)