Month to date

[#1]

Hi all,

im tyring to figure out how to show Month to date but only up to the week i have selected.

For example i'm selecting financial Year (FY), Month and the Financial Week (FiscalWeek)

I want to see data from the beginning of the month up until the end of the Fiscal week. This is what i have so far

sum( {$<FY = {$(#=Only(FY))},Day=,Year =, Week=,Quarter =>} CURSALES)

So our week 49 is 6th May to the 12th May. If i have selected a Fiscal Week, I need my Month to date to show sales for 1st May -12th May

Once i have this working i need to then show the previous year same period

Thanks for your help

Michael

[CELAMBARASAN]

Use Date Field for this type of requirements

Sum({<FY=, Day=, Year=, Week=, Quarter=, DateField={">=$(=MonthStart(Max(DateField))<=$(=Max(DateField)))"}>} CURSALES)

Hope it helps

[#3]

Hi,

Thanks for that, however it didn't give me the correct figures or the figures calculate the fuill month still.

For week 48 (1-5 May) i want to see a total of £9,697.35 for Location Ashton, for week 49 of £21,717.01 (1st -12th May)

I have attached a cut version of May data so that you can see what im doing.

Michael

[#4]

I've tried the same expression, using a text box to build "a factoid" of Month to Date and the expression works perfectly.

Sum({<FY=, Day=, Year=, Week=, Quarter=, DateField={">=$(=MonthStart(Max(DateField))<=$(=Max(DateField)))"}>} CURSALES)

I did some adaption of this set expression to build a vSetMTD set so that I could include in a inventory value calculation that uses multiple Movement and Consignment values.  The approach in nesting this in a set is by just capturing the expression within {} or - {<FY=, Day=, Year=, Week=, Quarter=, DateField={">=$(=MonthStart(Max(DateField))<=$(=Max(DateField)))"}>} - as your set.