Try to figure out Week to Date Sales

[fyyyzer]

I'm not a filemaker programmer by any stretch but I've used FM over the years for many a project. I've always wanted FM to provide the info below but have had to take it to Excel to get this kind of data. Any help you can provide would be greatly appreciated.

I own multiple restaurants and accrue sales data each day for each location. To keep things simple I'll pretend I'm only using the following fields:

Store

Date

Sales

I've been trying to figure out how to create the self-relationship and resulting calculation that would give me the Week To Date sales by location. I'm close to success as I'd figured out how to use a serial that references the first letter of the store name with the week of the year and then summing the result via a self-relationship.

Let's say the sales data is as such:

Mon $1000

Tue $1000

Wed $1000

Thu $1000

My calculation would return $4000 for the weekly sales which is correct. But if I'm editing/viewing the record for Tuesday I'd like it to report that the Week to Date sales were only $2000 as of Tuesday, $3000 as of Wednesday, etc.

I tried setting up a serial key that was Left( Store; 1) & "-" & WeekOfYearFiscal( Date; 2) &"-"& DayOfWeek & "-" & Year(Date)

the DayOfWeek field is: If(DayOfWeek(Date) = 1; 7; DayOfWeek(Date)-1) We view sales as Monday thru Sunday not as Sunday thru Saturday like a normal calendar.

I think I'm fairly close to figuring this out but just can't figure out the last little bit. Please help! ???

[Genx]

Try this:

Firstly, make 2 new stored calculations:

Calc 1 cWeekYear: WeekOfYearFiscal(Date;2) & "-" & Year(Date)

Calc 2 cDay: If(DayOfWeek(Date) = 1; 7 ; DayOfWeek(Date)-1)

We'll call table occurance one of your table Table1 and table occurance two of your table Table2.

Create a self join between the two tables with the following criteria (table1 is on left)

cWeekYear = cWeekYear

Location = Location

cDay <= cDay

Then we add one final calculation to our table:

cSalesSum (from table1) = Sum(Table2::Sales)

~Genx

[Ender]

I'd try a relationship based on the date range, where the range is determined on the parent side of the relationship based on the day of week. The keys would be:

Date (date)

WeekStartDate (calculation, date result) = GetAsDate(Date - Let(daynum = DayOfWeek(Date) - 1; Case(daynum = 0; 7; daynum)) + 1) //this calc can be unstored

And the relationship would then be:

Store = Store

AND Date ≥ Date

AND WeekStartDate ≤ Date

This way there is less overhead, and you don't have to worry about missing something at the end of the year with those WeekOfYear calcs.

[Genx]

But i wasnt completley wrong right? Lol someone tell me my way would have worked, i need closure

~Genx

[fyyyzer]

Bleary eyed I finally left the office before I had a chance to try either solution. I'll give 'er a shot in the morning though. You guys are amazing. It's definitely just having the right mindset (which I don't have). :laugh:

[Genx]

Right mindset? I just think its a whole bunch of repitition gradually getting sick of things not working properly or quickly enough, a bit of reading, and then leeching off all the guru's like ender,comment, soren, vaughan, lee, laretta, fenton to name a few - all who are happy to help people out - here on these wonderful forums

~Genx

[fyyyzer]

Seems to work great!!! Thanks Ender & Genx

This beast has evolved over the years and I've got so many relationship in there I couldn't see straight. Time to go back and read some of my Filemaker 7 books on relationships, etc. Thanks again!

[fyyyzer]

I thought I could figure out how to grab the same info from the year prior so I could figure out if we were up in sales week to date over last year or even two years ago. Short Answer: NOPE!

I managed to get the key that Ender provided to return the previous year with this calc (probably buggy as heck)

Date ( Month ( Key_WeekToDate ); Day ( Key_WeekToDate ) ; (Year (Key_WeekToDate)-1))

But how to I setup the relationship to grab the sum of sales for that week a year ago?

Ultimately, I'd like to get Week to Date, Month to Date and Year to Date numbers for this year and the prior years so that I can calc how we're doing against historical sales data.

I've enclosed a subset of my file if anyone wants to take a look at it.

Thanks again for any help that someone might be able to provide.

Daily_Sales_Copy.zip

[fyyyzer]

Can anyone else give me some guidance on this? I'm fairly stumped on this.

[Genx]

Just add another relationship, this one between this new field in your parent Table occurance and the related serial field in your child table...

Then just get a field to get the total of all monies in that relationship...

~Genx

[fyyyzer]

Thanks GenX. Ender used this to relate the two:

Store = Store

AND Date ≥ Date

AND WeekStartDate ≤ Date

I've tried different machinations of relating the WeekStartDateLastYear field I created against those and don't seem be be able to get a valid sum out of it.

[Genx]

Um, add an extra calc field,

If(Mod(Year(Date);4) = 0 ; date - 366 ; date - 365)

Then substitute this field for your date field in the new relationship, i think that'll work, though i don't remember the problem exactly lol... I tried reading it, but i've had to try and develop something confusing today... Sorry

~Genx