Calculating Month to Date and Week to Date

[#1]

Hello again.

I need to calculate sums of Week To Date and Month To Date. I thought using set analysis would cover this if I specified my current day (vToday) and my month start date (MonthStart(vToday)) as my range.

Is there a different way I should approach this before I dive any further into set analysis for this.

Here is the full code I have so far.

=sum({<day(vToday)-day(monthstart(vToday)>} Inventory.UNIT_CST_DLR)

I added the day part since I started this post thinking if I specified my actual day numbers, it would just give me the totals for those days and then create the sub total in my pivot table.

Again, any help or direction is greatly appreciated.

[fernandotoledo]

Nice Avatar!

Before going too deep into set analisys, try out the functions InYearToDate(), inMonthToDate(), inWeekToDate()

They work like

sum(if(inYearToDate(FieldDate,ReferenceDate,0),Value)

[#3]

Haha, thanks!

I knew there would be a function out there. I tried looking through the help file but I dont recall seeing anything about InYearToDate etc..

I will let you know if this works!

Thanks again!

[#4]

A little further explanation might be needed.

DATE_SOLD is the date that is selected for this tab based on the month and year (no day selected). I am using vToday for the current day's date. These are my two attempts at getting the totals for month to date. The first formula returns a 0 and the second returns a dash.

=sum(if(inMonthToDate(4,vToday,MonthStart(Sales.DATE_SOLD),0),Sales.SLS_QTY))

=if(InMonthToDate(vToday, Sales.DATE_SOLD, 0), sum(Sales.SLS_QTY))

What do you think I am doing wrong?