11 Replies Latest reply on Sep 16, 2011 1:49 PM by philmodjunk

    Relationship Help? Need Sales Comparison



      Relationship Help? Need Sales Comparison


      I need to compare sales on a daily basis by day of week and week of fiscal year, on a per-location basis. I've managed to get the calendaring worked out, but I don't know how to approach the prior year sales. I'd like to do the past three years, ideally.

      I tried to set up field in Sales for the PY1 numbers, but can't get it to populate with the data no matter how hard I try. Open to anything that works, even if it's a different solution entirely. I'm not married to anything but the end result.

      Thank you.



        • 1. Re: Relationship Help? Need Sales Comparison

          What value does "FiscalYear" store? Is it a number such as 2011?

          That would make PY1 simply FisacalYear - 1.

          • 2. Re: Relationship Help? Need Sales Comparison

            Yes, it's just FiscalYear -1

            • 3. Re: Relationship Help? Need Sales Comparison

              But what data is stored in FiscalYear? This is a very straight forward calculation so we need to figure out why it "isn't populating". This field should be either a field of type calculation or a number field with an auto-entered calculation. If using the auto-entered calculation option, you won't see records that existed before you defined the calculation with any data in this field, you'd need to change it to a field of type calculation or use Replace field contents with the calculation option to update the contents of the field.

              Or is it that you have the correct value in PY1, but can't get records in Sales to link to PreviousYearSales?

              • 4. Re: Relationship Help? Need Sales Comparison

                FiscalYear is a straightforward calc, and it works fine. I wanted to use it in PY1CalcSales. I guess it isn't really necessary, it's just what popped into mind at the time, since I'm new and not great with date calculations inside of other calculations.

                I'm going to upload a different screenshot, I think it will make more sense when you see what I'm trying to do, vs trying to explain it in words.

                Thanks for your time.

                • 5. Re: Relationship Help? Need Sales Comparison

                  Ok, you are trying to use an if functiont to specify which related record matches to your current sales record and that's why this isn't working.

                  Modify your relationship to be:

                  Sales::StoreID = PrevYearSalesSameDay::StoreID AND
                  Sales::PY1 = PrevYearSalesSameDay::FiscalYear AND
                  Sales::day = PrevYearSalesSameDay::day

                  You can do this by dragging from StoreID to StoreID in manage | Database | Relationships, or you can double click the relationship line and add the second pair of match fields there.

                  Then you do not need any calculation field, you can just add PrevYearSalesSameDay::calculated sales to a layout based on Sales. If you do need it in a calculation field (you need this once in a while....), you can simplify the calculation do to just: PrevYearSalesSameDay::calculated sales.

                  This works for pulling up a single record for the same day of the previous year. Matching by same week suggests that you may be matching to 5 - 7 records from the previous year. That will require both an added relationship with a new occurence of sales and the use of either the Sum function or a summary field to compute a weekly total from the previous year.

                  Sales::StoreID = PrevYearSalesSameWeek::StoreID AND
                  Sales::PY1 = PrevYearSalesSameWeek::FiscalYear AND
                  Sales::week = PrevYearSalesSameWeek::week

                  Sum ( PrevYearSalesSameWeek::calculated sales ) or a "total of" summary field that computes the total of calculated sales and added from the PrevYearSalesSameWeek table occurrence can produce such a weekly total.

                  • 6. Re: Relationship Help? Need Sales Comparison

                    I really do appreciate your help. I do. And maybe I'm just dense, but I can't get it to work.

                    Maybe it would help to explain the Day/Week issue. Day is actually DayName and Week is really WeekofYear (adjusted to match corporate). Example: We need to compare Friday's sales in Week 36, across multiple years. There will only be one DayName in each WeekofYear for any given year. 

                    I've changed the relationships to the following:

                    Sales::PY1 = PrevYearSales::FiscalYear AND

                    Sales::StoreID = PrevYearSales::StoreID AND

                    Sales::Day = PrevYearSales::Day AND

                    Sales::Week = PrevYearSales::Week

                     and the calc field in Sales to:

                    Sales::PY1CalcSales = PrevYearSales::Calculated Sales


                    and it's blank. Nothing. I do need to perform calculations on this field. I've tried everything I can think of, and nothing so far has worked, which is why my initial question today came back to the relationship and if there was something wrong there. I've tried this variation before, and I can't seem to make it work. =/



                    Edited to add: I've been working in table view, when I grab PrevYearSales::Calculated Sales and put it on the layout, it says <Index Missing>. I've messed around with trying to store the values in the calculations used, but I get the following error:


                    The calculation “Week” cannot be stored or indexed because it references a related field, a summary field, an unstored calculation field, or a field with global storage.


                    • 7. Re: Relationship Help? Need Sales Comparison

                      There will only be one DayName in each WeekofYear for any given year.

                      OK, then you need both Day and week fields in the same relationshiop in order to match to a record from the same day of the same week as the previous fiscal year as you have done.

                      You may have the wrong data types here in your  day or week fields. Make sure that Day is a field of type text (if it stores day names like Sunday, Monday....) or a calculation field with "text" selected in the return type drop down inside Specify calculation.

                      In similar fashion, the Week field should have a type/return type of Number and not text--but this is less likely to the cause of the problem here as text should still match values correctly.

                      How will you use the PY1calcSales field? (There's a good chance that you do not need it at all here.)

                      • 8. Re: Relationship Help? Need Sales Comparison


                        I did have the wrong data type in Day, (thank you) but that hasn't fixed the problem.


                        I need to re-create an existing excel sheet. We are adding locations left and right, and excel is getting crazy. It looks something like this, where Comp Sales are the prior years' sales, and the % is just =(Sales/CompSalesPY1)-1 and formatted as a percentage.

                        Store Sales  Comp Sales (PY1) % Comp Sales (PY2) %  
                        200 12345. 13579


                        • 9. Re: Relationship Help? Need Sales Comparison

                          Without actually inspecting your file, I don't have anything else to suggest. This should work, I've used similar methods myself with no trouble, but can't spot what's missing from here.

                          You can upload a clone (empty copy) of your file to a file sharing site if you want to and then post the download link here so that I and others can take a look at it.

                          • 10. Re: Relationship Help? Need Sales Comparison



                            Here you go. Again, I appreciate all the help. The only thing that I can figure out is that Week isn't stored or indexed. I'd love it if you would take a look and see what I've done wrong.

                            • 11. Re: Relationship Help? Need Sales Comparison

                              That's the issue right there, the field must be indexed before it can be used on both sides of the relationship like this and unstored fields cannot be indexed. You'll need to find a new way to calculate the Week field so that it is a stored field.