Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI All,
Is it possible please to use set analysis with the function 'inweek' ? i.e. I have this to convert ..
sum( if ( inweek (MyTxnDate, today(2), -52 and DayOfWeek=Mon, Sales))
........so the 'DayOfWeek=Mon' converts to set analysis easy enough, but when I try to convert the inweek, any syntax I use throws up an error. I am trying to convert as the calculation currently is so sloooooowwwww.
Thanks in advance
Bob
Just make a start and end date variable.
vRelevantSalesStartDate
=weekstart(today(2)-52*7))
vRelevantSalesEndDate
=weekend(today(2)-52*7))
Expression
sum({<MyTxnDate={">=$(vRelevantSalesStartDate)<=$(vRelevantSalesEndDate)"}>}Sales)
I'm not sure of the exact "Monday" complexities that you need to include, but whatever you need to do can just be done in these 2 variables. That's why using variables with date set analysis is so nice--saves you a lot of work with maintenance.
Regards,
Vlad
Looks like you're missing a ) parentheses. But I would suggest you save yourself a lot of headaches and just create a variable. Looks like you're trying to get the Monday that occurred 52 weeks ago. Something like this should work:
vRelevantSalesDate
=weekstart(today(2)-52*7))
Expression
sum({<MyTxnDate={'$(vRelevantSalesDate)'}>}Sales)
Regards,
Vlad
HI Vlad,
Thankyou - your formulae would indeed for just the monday, but then if I need the whole week i.e.
sum( if ( inweek (MyTxnDate, today(2), -52), Sales))
.. you are not aware of a 'set analysis' logic that could work with this please ?
Thanks
Bob
Just make a start and end date variable.
vRelevantSalesStartDate
=weekstart(today(2)-52*7))
vRelevantSalesEndDate
=weekend(today(2)-52*7))
Expression
sum({<MyTxnDate={">=$(vRelevantSalesStartDate)<=$(vRelevantSalesEndDate)"}>}Sales)
I'm not sure of the exact "Monday" complexities that you need to include, but whatever you need to do can just be done in these 2 variables. That's why using variables with date set analysis is so nice--saves you a lot of work with maintenance.
Regards,
Vlad
Thanks Vlad - I will use your logics 🙂