12 Replies Latest reply on Jul 27, 2018 10:39 AM by Tony Smith

    Five week filter report

    Tony Smith

      Hi All,

      Can someone please help me with the query below.

       

      I want to show the current five weeks sales and the same five weeks for the previous year based on the 'BBCA weeks in fin year' field.

       

      I want to compare the current five weeks data with the previous year's five weeks transaction sales.

       

      Also, compare the current five weeks of the budget to the previous year transactions sales

       

      I have attached a spreadsheet to illustrate what I am trying to achieve.

       

      I used the Report Date field to recognise the current BBCA week in fin year.

       

      For example, today is 26th July 2018, so the current BBCA week in fin year is 1920 because we now have a completed week according to the BBCA week in fin year date.

       

      I have attached twbx package that shows the details of the BBCA week in fin year date.

       

      Thanks

       

      Regards

      Tony

        • 1. Re: Five week filter report
          Joe Oppelt

          I'm looking at this.

           

          I made this sheet:

           

           

          7/26 gives me week 1921, so I guess I don't understand the algorithm here that gave you 1920.

           

          Nonetheless, are you trying to compare 1921 (or 1920) and the 4 weeks prior with 1821 and the 4 weeks prior?

          • 2. Re: Five week filter report
            David Maning

            Hi,

             

            Here is the answer:

            IF

                (INT([BBCA Week In Fin Year]) >

                {MIN(IF

                    DATETRUNC('week', NOW())

                    =

                    { FIXED [BBCA Week In Fin Year]:MIN(

                    IF DATETRUNC('day', NOW()) = DATETRUNC('day', [Report Date])

                    THEN DATETRUNC('week', NOW())

                    END)}

                THEN INT([BBCA Week In Fin Year]) - 5

                END)}

                AND INT([BBCA Week In Fin Year]) <=

                {MIN(IF

                    DATETRUNC('week', NOW())

                    =

                    { FIXED [BBCA Week In Fin Year]:MIN(

                    IF DATETRUNC('day', NOW()) = DATETRUNC('day', [Report Date])

                    THEN DATETRUNC('week', NOW())

                    END)}

                THEN INT([BBCA Week In Fin Year])

                END)})

            OR

                (INT([BBCA Week In Fin Year]) >

                {MIN(IF

                    DATETRUNC('week', NOW())

                    =

                    { FIXED [BBCA Week In Fin Year]:MIN(

                    IF DATETRUNC('day', NOW()) = DATETRUNC('day', [Report Date])

                    THEN DATETRUNC('week', NOW())

                    END)}

                THEN INT([BBCA Week In Fin Year]) - 105

                END)}

                AND INT([BBCA Week In Fin Year]) <=

                {MIN(IF

                    DATETRUNC('week', NOW())

                    =

                    { FIXED [BBCA Week In Fin Year]:MIN(

                    IF DATETRUNC('day', NOW()) = DATETRUNC('day', [Report Date])

                    THEN DATETRUNC('week', NOW())

                    END)}

                THEN INT([BBCA Week In Fin Year]) - 100

                END)})

            THEN 'show'

            END

             

            pls find update worksheet attached.

             

            Trust this helps.

            D

            • 3. Re: Five week filter report
              Tony Smith

              Hi David,

               

              Thank you for all your help.

               

              I got the error message when I tried to open the attachment:

              Errors occurred while trying to load the workbook. The load was not able to complete successfully.

               

              I am using version 10.5. Can you please re attach the files.

               

              Regards

              Tony

              • 4. Re: Five week filter report
                Tony Smith

                Hi Joe,

                 

                Yes, you are right. The current week is 1921, however I want to see data for a complete week and week 1921 has not yet completed.  If I run the report on the 29th July  2018 when we will have completed week 1921, the current week will be 1921.

                 

                Thank you

                 

                Regards

                Tony

                • 5. Re: Five week filter report
                  Joe Oppelt

                  OK, Tony.  So one way or another you know the week you need to grab.  And your data has a really nice field [BBAC Week in fin...] to guide you to the correct data.

                   

                  Regarding that field, if there is no reason for it to be a string field, it would be more efficient to make it numeric.  Either do that in generating the data source initially, or make a calc that looks like this:

                   

                  INT([BBAC Week...])

                   

                  So lets say you can determine that you need 1920.  Therefore you need 1920 back to 1916.

                   

                  Make a calc that holds the last week you need.  Call it [final week].

                   

                  Now make this calc:

                   

                  If ([BBAC Week Int] <= [Final week] and [BBAC Week Int] >= [Final week]-4)

                  or

                  ([BBAC Week Int] <= [Final week]-100 and [BBAC Week Int] >= [Final week] - 104)

                  then 1 else 0 END

                   

                  Put that on filters.  Select for value = 1.  You'll get only the weeks you need.

                  • 6. Re: Five week filter report
                    David Maning

                    Pls find the updated workbook.

                     

                    Considering your last update regarding the week the formula can easily amended to include this condition.

                     

                    D

                    • 7. Re: Five week filter report
                      Joe Oppelt

                      See attached.  Sheet 5.  Put that filter calc on filters and select for 1.

                      1 of 1 people found this helpful
                      • 8. Re: Five week filter report
                        Tony Smith

                        Hi Joe,

                         

                        In your code I can see - 100 and - 104 in it. Can you please explain to me what the purposes of these numbers are?  This is just for my understanding. Thank you for all your help

                         

                        Thank you

                         

                        Regards

                        Tony

                        • 9. Re: Five week filter report
                          Tony Smith

                          Hi David,

                           

                          Using the code I can see week 1921 data.  Based on the BBCA calendar this week is not yet completed. I expect to see week 1921 when I run the report on 29th July 2018.  We only refer to a current week when we have a completed week.

                           

                          Also, I can see - 100 and - 105 in your code. Can you please explain to me what the purposes of these numbers are?  This is just for my understanding. Thank you for all your help

                           

                          Regards

                          Tony

                          1 of 1 people found this helpful
                          • 10. Re: Five week filter report
                            David Maning

                            Hi Tony,

                             

                            In order to catch the condition of BBCA week determination based on max date in this week use this calculation:

                            {MIN(

                            IF DATETRUNC('day', NOW()) = { FIXED [BBCA Week In Fin Year]:MAX(DATETRUNC('day', [Report Date]))}

                            THEN INT([BBCA Week In Fin Year])

                            ELSE

                                IF  DATETRUNC('day', NOW()) = DATETRUNC('day', [Report Date])

                                THEN INT([BBCA Week In Fin Year]) - 1

                                END

                            END

                            )}

                             

                            The previous calculation is amended correspondingly:

                            IF

                                (INT([BBCA Week In Fin Year]) >

                                {MIN(IF

                                    DATETRUNC('week', NOW())

                                    =

                                    { FIXED [BBCA Week In Fin Year]:MIN(

                                    IF DATETRUNC('day', NOW()) = DATETRUNC('day', [Report Date])

                                    THEN DATETRUNC('week', NOW())

                                    END)}

                                THEN INT([BBCA Week In Fin Year Determination]) - 5

                                END)}

                                AND INT([BBCA Week In Fin Year]) <=

                                {MIN(IF

                                    DATETRUNC('week', NOW())

                                    =

                                    { FIXED [BBCA Week In Fin Year]:MIN(

                                    IF DATETRUNC('day', NOW()) = DATETRUNC('day', [Report Date])

                                    THEN DATETRUNC('week', NOW())

                                    END)}

                                THEN INT([BBCA Week In Fin Year Determination])

                                END)})

                            OR

                                (INT([BBCA Week In Fin Year]) >

                                {MIN(IF

                                    DATETRUNC('week', NOW())

                                    =

                                    { FIXED [BBCA Week In Fin Year]:MIN(

                                    IF DATETRUNC('day', NOW()) = DATETRUNC('day', [Report Date])

                                    THEN DATETRUNC('week', NOW())

                                    END)}

                                THEN INT([BBCA Week In Fin Year Determination]) - 105

                                END)}

                                AND INT([BBCA Week In Fin Year]) <=

                                {MIN(IF

                                    DATETRUNC('week', NOW())

                                    =

                                    { FIXED [BBCA Week In Fin Year]:MIN(

                                    IF DATETRUNC('day', NOW()) = DATETRUNC('day', [Report Date])

                                    THEN DATETRUNC('week', NOW())

                                    END)}

                                THEN INT([BBCA Week In Fin Year Determination]) - 100

                                END)})

                            THEN 'show'

                            END

                             

                            I used 105 and 100 in order to catch this difference in periods  which is equal to one year.

                             

                            Pls mark my response as correct and helpful in order to close this thread.

                             

                            Updated worksheet is in the attachment.

                             

                            Trust this helps.

                            D

                            1 of 1 people found this helpful
                            • 11. Re: Five week filter report
                              Joe Oppelt

                              Tony Smith wrote:

                               

                               

                               

                              In your code I can see - 100 and - 104 in it. Can you please explain to me what the purposes of these numbers are? This is just for my understanding. Thank you for all your help

                               

                               

                               

                              In your "Transaction Data" sheet you show BBCA year 19, with BBCA Weeks of 1916 through 1920.  ANd for year 18 you show BBCA weeks of 1816 through 1820.


                              In my sheet 5 I made an integer equivalent of your BBCA Week field.  (See [BBCA Week Int].)

                               

                              I also calc'd a [Final Week].  Look at the logic in there.  For now I just subtracted one from the BBCA Week associated with whatever TODAY() is.  It calcs as 1920 today.

                               

                              Then I made [Filter Weeks].  It is setting a 1 or 0 for the rows in the weeks I want to grab.  My goal is to collect 1816-through-1820, and 1916-through-1920.  In one "or" section of that calc I grab 4 weeks earlier than [Final Week] through [Final Week] (which today are 1916-through-1920).  In the other "or" section I grab weeks that are the same range, minus 100.  (1816-through-1820).  So I grab value from negative-4 through zero in one half of the logic, and negative-104 through negative-100 in the other half.  This works for me by virtue of changing the original BBCA Week strings to integers.

                               

                              You will see on Sheet 5 that all the rows within those two ranges have a value of 1 for my [filter weeks] calc.  All other rows have zero.  When you put that calc on FILTERS and select for value=1, you'll just get those two stretches of target rows.

                              1 of 1 people found this helpful
                              • 12. Re: Five week filter report
                                Tony Smith

                                Hi David /Joe,

                                 

                                Thank you for your help on this query and your explainations on the solution.  I really appreciate it. Both solutions give me what I wanted.

                                 

                                Regards

                                Tony