9 Replies Latest reply on Jan 23, 2018 11:43 AM by Joe Oppelt

    How to countd SKU new, remain, remove, current over time

    Trần Ngọc Sơn

      Hi all,

       

      I have data for sales.

       

      I want to count SKU change over time ex in excel.

           In  January, I have 4 SKU (A,B,C,D); February I have 3 SKU (B,D,E). 

                Two month have the same SKU B,D -> SKU remain. 

                January have A,C but February don't have -> SKU remove.

                E -> Is new SKU

                Current SKU is countd(SKU) in current month,

       

      How I can count in Tableau? I don't know how to compare SKU over time.

        • 1. Re: How to countd SKU new, remain, remove, current over time
          Norbert Maijoor

          Hi Tran,

           

          Find my initial approach as reference below and stored in attached workbook version 9.3

          I am not able to get a "rock solid" or "final" solution and would like to ask Joe Oppelt if is could take it a step further. Upfront thanks a TON:)

           

           

          • 2. Re: How to countd SKU new, remain, remove, current over time
            Trần Ngọc Sơn

            Very happy for your repply. I think today is your holiday in your country.

            Thanks for your information.

            I think I need more time to consider about it. Because I want to ceate measures to see trendline of the change in SKU structure.

            • 3. Re: How to countd SKU new, remain, remove, current over time
              Joe Oppelt

              Tran -- Norbert mentioned me in his reply.

               

              Start with his example.  Decide what you actually want to do, and we can move forward from there.

              • 4. Re: How to countd SKU new, remain, remove, current over time
                Trần Ngọc Sơn

                Joe

                This is my purpose:

                 

                The Measures Remain, Remove, New is calculated as picture.

                Remain: the number SKU has in 2 month adjacent. Ex: In February: January and February have 2 SKU B and D,

                With the same calculated, Measures Remain have value 2 in February,1 in March (SKU E), 2 in April (SKU E and F), 0 in May.

                 

                Remove: the number SKU has in last month but don't exist in current month. Ex:   SKU A and B have in January but in February, SKU A and C don't exist.

                With the same calculated, Measures Remove have value in the same excel.

                 

                New: Opposite Remove, the number SKU has in Current month but don't exist in last month. Ex: SKU E in February, but don't have SKU E in January.

                .

                Current SKU is the number SKU in current month. Ex: in February, have SKU B,D,E -> Current SKU have value 3 in February.

                 

                In Tableau, it's dificult to compare element in dimension between 2 month adjacent over time. I don't idea for problem in Tableau ( just idea custom SQL, but it don't effective in my case)

                • 5. Re: How to countd SKU new, remain, remove, current over time
                  Joe Oppelt

                  See attached.  Start with Sheet 3.

                   

                  This sheet sort of simulates the second screen shot in your last  reply.

                   

                  I did some things, though, that help me see what's going on.  First of all, I made a duplicate of the data source, and blended on SKU and MONTH.  There is a calc, [SKU Calculated] that lets me insert a value where no value currently exists.  You'll see why I did that in the next steps.  I also created an [index] calc that lets me see how I am walking through the table.  In sheet 3 you will see the results of the two calcs.  I told the index calc (which is a table calc) to run along months so that I get 1,2,3,4,... in each row.

                   

                  Now look at sheet 4.  I made a calc, [New for month], that decides whether the current value is new (or is "x", which means nothing is really there.)  I took index off the text, and added this new calc to the text so you can see what the results are.  I told this calc to run along [Month] just like [index] does.  So, moving across the SKU row, I determine if this one new or not.  Set 1 or 0 accordingly.

                   

                  You can do the same sort of thing for the other calcs.

                   

                  Now go to sheet 5.  Here I moved SKU from ROWS to DETAIL.  (SKU needs to stay on the sheet, but it doesn't need to get displayed in the end.)  I made a new calc, [Total New for Month].  This is a window sum, and I told it to run along SKU (essentially, TABLE(down), though that's not a specific option for me to choose.  So I forced that by telling tableau o run down the columns along SKU.  You see the values you want for NEW:  4,1,1,3,2,1.  Because SKU is still on the sheet, we get panes of values for each SKU.  To get rid of that, go to Sheet 6.

                   

                  Here I put [index] on the filter shelf.  And for that pill, I want it to run DOWN the table, like the window_sum.  Notice that [index] has pills in two different places on the sheet, and each one has a different table-calc setting.  Tableau can do this!  The filter [index] runs down the table, and I select only value = 1. Now we get one row for the numbers you want.

                   

                  And if you want to chart that on a line graph, see sheet 7.

                   

                  Replicate the same for the other measures.

                  1 of 1 people found this helpful
                  • 6. Re: How to countd SKU new, remain, remove, current over time
                    Trần Ngọc Sơn

                    Thank you very much.

                    I understand how you to do. It's a part of solutions for my problems.

                     

                         1. But you can help me understand why don't use Month continuous in your solutions?

                         2. And how i isolation New SKU as same as measure?

                         3. The final target is draw trendline for SKU analysis base on New SKU, Remain SKU, Remove SKU in one chart.

                     

                    I appreciate your effort, the guide is very detail and easy to understand how to do. Thank Joe Oppelt very much. 

                    • 7. Re: How to countd SKU new, remain, remove, current over time
                      Joe Oppelt

                      Change month to continuous, and you'll see that the viz totally changes, and tries to run the lines by SKU instead of by discrete values.

                       

                      A discrete month pill acts just like the string pill you had before, only by having the value as a date we get the numerical sequencing of months, and that allows the indexing to work the way we need it to work.

                       

                      Now you can follow the same model I did for NEW, and create the same series of calcs for REMAIN and REMOVE.  The [Total New for Month] calc is one measure, and when you create the same for REMAIN and REMOVE, you will have three separate measures.  You can use the MeasureName/MeasureValue method to display multiple measures in one chart.

                      • 8. Re: How to countd SKU new, remain, remove, current over time
                        Michael Ye

                        Joe,

                         

                        This answer is very helpful to me. Thanks a lot.

                         

                        Here, I still have one question: I don't understand why you blended the data source with its copy? In attachment, I deleted the copy of the data source and modified the "SKU Calculated" to the following calculation:

                         

                        if isnull(ATTR([SKU])) then "x" else ATTR([SKU]) end

                         

                        It looks that I get the same results as yours.

                         

                        Thanks for consideration.

                         

                        Michael Ye

                        • 9. Re: How to countd SKU new, remain, remove, current over time
                          Joe Oppelt

                          Michael -- You're right. 


                          Blending a data source to itself is a way to force dimensional data where none exists, but in this case wrapping the dimension in ATTR() forces Tableau to look at it at the aggregate level, and ends up serving the same purpose without the extra copy of the data source.

                          1 of 1 people found this helpful