5 Replies Latest reply on Jun 30, 2015 8:09 AM by laguna92651

    Filter Day of Week sales by location

    laguna92651

      Title

      Filter Day of Week sales by location

      Post

      I have the following relationships setup. I am summing the sales from the dash_INVOICE occurrence based on the day of the week the invoice was written, the day is extracted from the invoice date for a given year. The summed field, csum_InvoiceSale, is in the dash_DAY_OF_WEEK occurrence, this is then placed on a portal on the dashboard, showing sales by Sunday, Monday, Tuesday etc., this works fine.

      I would like to also filter the csum_InvoiceSale on the dash portal by location, what would be the approach to apply this filter. The location field is also on the dash_INVOICE, I would like to filter by one of 3 locations or show all locations, ideally I would like to handle additional locations automatically, not hard wire the locations in.

      Screenshot_-_Sales_By_Day.png

        • 1. Re: Filter Day of Week sales by location
          philmodjunk

          What match fields do you currently have linking dash_day_of_Week to dash_Invoice||id_day_of_week? I can see that you have more than one but not what they are.

          One option is to replace the calculation field in dash_day_of_Week with a summary field in the far right table. You can then specify a portal filter in Dash that specifies a particular location. You could set up different portals for each location where the only difference is teh location specified in the filter. (This produces "display only" sub totals. You can't use them in other script steps or calculations.)

          Other options are also possible--including the use of ExecuteSQL if you are using FMP 12 or later.

          • 2. Re: Filter Day of Week sales by location
            laguna92651

            The match fields for the relationship between dash_DAY_OF_WEEK and dash_Invoice are:

            id_day_of_week = ID_DAY_OF_WEEK

            Date_Start <= Date_Invoice

            Date_End >= Date_Invoice

            • 3. Re: Filter Day of Week sales by location
              laguna92651

              I put the Totals summary fields from the dash_INVOICE on the dashboard portal, the portal is based on dash_DAYS_OF_WEEK, it shows the correct totals by day of the week from the dash_INVOICE occurrence.

              I tried to filter the portal by Project_Name (location) but I didn't get a change in Totals sum, when I used one particular Project Name and got nothing when I tried two other Project Names.

              The filter expression in the portal
              dash_INVOICE||id_day_of_week::Project_Name="Gallery 2015"

              I have not changed any of the relationships from the original.

              Just to be clear this is what I want on the portal.

              Sunday    1000
              Monday     500
              Tuesday   2500
              Wednesday 3000
              Thursday  1500
              Friday    2200
              Saturday  1800

              • 4. Re: Filter Day of Week sales by location
                laguna92651

                Do I need to set up the relationships differently?

                Is dash_INVOICE||id_day_of_week::Project_Name="Gallery 2015" the correct way to filter the portal?

                Thanks for the help.

                • 5. Re: Filter Day of Week sales by location
                  laguna92651

                  Finally got the portal to work, going back to problem periodically while doing other stuff, looks like I got a field mixed up, names too similar.

                  Ran into another problem moving forward, I am summing the daily amounts in the INVOICE_SELECT_LOCATION TO by location and as mentioned get the correct results for a location fine on the portal.

                  I need to chart the portal data by days of the week, I am using Google charts for charting because the native FM charting doesn't work in runtime. I have set up 7 unstored calculation fields in the Dash TO to store the sums from the portal using GetTheNthRecord function, I then use the 7 unstored calculation fields that have the 7 daily sums in them in the Google charts html code. The GetTheNthRecord function does not retrieve the correct data, each of the 7 DOW_Location fields has the total for all sales for the selected location.

                  I used the same approach on another set of similar TO's and the GetTheNthRecord function worked fine. However in that case the summing was done in the middle TO to sum all locations by days of the week.

                  Unstored Calculation, the sum_subtotal_price is the same field as on portal.
                  GetNthRecord (dash_INVOICE_SELECT_LOCATION||id_DOW|::sum_subtotal_price;2 )

                  Relationships between dash_DOW_SELECT_LOCATION and dash_INVOICE_SELECT_LOCATION

                  ID_Day_Of_Week = id_day_of_week
                  DateStart <= DateInvoice
                  DateEnd >= DateInvoice
                  Filter_ID_ProjectLocation = id_project