5 Replies Latest reply on Jan 29, 2018 6:43 AM by Norbert Maijoor

    How to find product code in every year

    Ed Yeldham

      Hello,

       

      I have some simple sales data of product code and date. I have replicated the query in the sample data as below.

       

      I want to know the products which appear in all years.  Using the example below I would exclude the ACCO 3 HOLE PUNCH RECYCLE as it did not have an order in 2015.  I would want to see ACCO BINDER CLEAR though as it has data in all 4 years.

       

      I need to generate some sort of calculated field which I could then use in the filter shelf to analyse sales values and sku counts of consistent "core" products.


      Help

       

       


      Ed

       

       

       

        • 1. Re: How to find product code in every year
          Norbert Maijoor

          Hi Ed,

           

          Find my approach as reference below and stored in attached workbook version 10.3 located in the original thread

           

           

          1. M1. Count year per Product: {fixed [Product Name]:countd(year([Order Date]))}

           

          2. M2. Count Years: {fixed: countd(year([Order Date]))}

           

          3. D1. Display all years: [M1. Count year per Product]=[M2. Count Years]

           

          4. Drag all required objects to the indicated locations and filter D1. Display all years on True

           

           

          Regards,

          Norbert

          1 of 1 people found this helpful
          • 2. Re: How to find product code in every year
            Ed Yeldham

            Norbert,

             

            That is nearly the answer - in my data set I have data back to 2004.  I'm only looking at the last 5 years so have filtered this on my shelf accordingly.  I think the calculated field will look at all my dates though so it will look back to 2004.  If I built a parameter to filter on (ie select date from) then filtered on dates after this or even between 2 dates how would I build this into the formula's below?

             

            1. M1. Count year per Product: {fixed [Product Name]:countd(year([Order Date]))}

             

            2. M2. Count Years: {fixed: countd(year([Order Date]))}

            • 3. Re: How to find product code in every year
              Norbert Maijoor

              Hi Ed,

               

              Find my updated approach as reference below and stored in attached workbook version 10.3 located in the original thread.

               

              1. D1. Display only Last 5 Years: DATEDIFF('year',[date],today())>0 and DATEDIFF('year',[date],today())<6

               

              2. M1. Count per Product Name: (Countd(year([date])))

               

              3. M2. Count per Product=5: (Countd(year([date])))=5

               

              4. Drag the required objects to the indicated locations and filter D1. Display only Last 5 Years & M2. Count per Product=5 on True

               

               

              Regards,

              Norbert

              • 4. Re: How to find product code in every year
                Ed Yeldham

                Hello,

                 

                Tried that and it doesn't work with my data?

                 

                Attached is an extract of my data but when I put the count on the filter shelf I don't have any "true" results.

                 

                As an aside I had planned to develop further (when it worked) to use a start and end parameter control so I could dynamically look at active products between 2 dates.........

                 

                Any thoughts on why it doesn't work?


                Ed

                • 5. Re: How to find product code in every year
                  Norbert Maijoor

                  Hi Ed,

                   

                  Not sure if my approach is "as desired;)"

                   

                   

                  1. D1. Fixed:  {fixed [Product Code]:countd(year([Trans Date]))}

                   

                  2. D2. Fixed Year: {fixed :countd(year([Trans Date]))}

                   

                  3. D3. Display: [D2. Fixed Year]=[D1. Fixed ]

                   

                  4. Drag D3. Display on filter and set filter to True

                   


                  Regards,Norbert