Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi i am working on set analysis for lastyear mtd values
i am calculating with variables
below are my variable declaration
for
LastYearMonthStart (vMnthStartLY) : =Date(MonthStart(AddMonths(Max(CalDate),-12)),'DD/MM/YYYY')
LastYearMonthToDate (vLyMtd) :=Date(Max(CalDate)-364,'DD/MM/YYYY')
below is my set Expression
Sum({<CalDate={'>=$(vMnthStartLY) <=$(vLyMtd)'}>}LY_DAY_NET_SALES)
the above set expression is giving zero values between dates in the variable but when i hard code particular dates it is giving correct results .
but my variables used in the set expresiiion are also giving the correct dates .
please fnd the attachment
Any help is appreciated
Thanks
have you tried my solution?
could you please share the sample app??
I have tried your solution but no luck .
The formular which you have given for Date(MonthEnd(Max(CalDate),-12)) is giving monthend date but i need mtd for lastyear MTD.
for Eg: if i select 20/02/206 it should give mtd for the last year i.e 21/02/2015 and should be based on day .20/02/206 is saturday and 21/02/2015 is saturday. so mTd is based on day but not on dates.
we want to compare sales based on day .
I tried with your set expression it is giving highest value.
for eg: i have data for 10th feb 2016 to 20 feb 2016
i have data ly_day_net_sales and it is displayign correct results but i am working for mtd values it is taking sum of all net sales between 10th feb 2015 and 21 feb 2015 (As it is last year sales).
and selection of date is not getting applied on mtd column
Thanks .
Change 2nd Variable as below
Date(AddMonths(Max(CalDate),-12))
Rest all as it is as per my previous answer.
EDITED: This will not work as per your requirements.
Can you provide sample data please?
Sum({<CalDate={'>=$(=vMnthStartLY) <=$(=vLyMtd)'}>}LY_DAY_NET_SALES)
Create tow variables in Variable Overview as below
vMnthStartLY
Date(MonthStart(Max(CalDate),-12))
vMnthDayLY
Date((Max(CalDate)-364))
Make sure that in Variable definition you should not use = in front of any Variable expression
Now use below expression
=SUM({<CalYear= , CalMonth = , CalDate = {">=$(=$(vMnthStartLY))<=$(=$(vMnthDayLY))"}>}LY_DAY_NET_SALES)