Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all.
Can anyone see what is wrong with this Set Analysis? I am trying to get Sales of Last year's, in this case July 1st - July 26th but am getting unexplained value
SUM({<YR=, MO=, DA=, QTR=, MonthYear=, TranDate={">=$(=Date(MonthStart(today(),-12))) <=DATE(AddMonths(Today(),-12))"}>}OrderValue)
TranDate is M/D/YYYY (7/26/2016)
My calendar is selecting Year = 2016
Thanks
SUM({<YR=, MO=, DA=, QTR=, MonthYear=, TranDate={">=$(=Date(MonthStart(today(),-12)))
<=$(=DATE(AddMonths(Today(),-12)))"}>}OrderValue)
Give a try with below expression:
SUM({<YR=, MO=, DA=, QTR=, MonthYear=, TranDate={"$(= '>=' &Date(MonthStart(today(),-12))) & '<=' &DATE(AddMonths(Today(),-12))"}>} OrderValue)
I thought so too. But when using the <=$(=DATE(AddMonths(Today(),-12)))"}>} portion the return value is $0.00
And that is incorrect
ok and this
SUM({<YR=, MO=, DA=, QTR=, MonthYear=,
$(=NUM([TranDate])) ={">=$(=NUM(MonthStart(today(),-12)))
<=$(=NUM(AddMonths(Today(),-12)))"}>}OrderValue)
Same result as my original expression. ... unexplained value...
I am trying to put a sample together.
As a test, you can put the two $-sign expansions in a text box to see what they produce and whether this set analysis spec is able to select records. Like
='>=$(=Date(MonthStart(today(),-12)))' & chr(10) &
'<=$(=Date(AddMonths(Today(),-12)))'
Best,
Peter
[Edit] I used Vineeth's expression because I think the original one produces an invalid set modifier