10 Replies Latest reply on Sep 12, 2016 10:04 PM by chun wey teo

    LOD on weeks & YTD

    chun wey teo

      Hi, I need to complete the following objective but only mange to complete partial of it. Need someone to help on this to get this done.

       

      Objective:  Count the number of customer who made nth purchase across week period.  Depending on the week filter chosen (YTD), the number of customer will change accordingly and display their nth purchase in each week.

       

      For example, customer A has did 2 purchases in week 35.  This will be shown as 2 purchase in week 35.  In week 37, he did another purchase and it should show 3 purchase under week 37 instead of 1. 

       

      Current Status: only manage to show the 2 purchase in week 35 but cant seem to show the purchase in week 37.  It was found under 1 purchase in week 37 and not 3 purchase.

       

      Can some one help on this?

       

      Attached is the screenshot and workbook.  Sales Customer ID who has such behavior -> 35112247 , 41151158

      Column: Sales Week

      Row: No of Purchase made

      Data: No of customer who made nth Purchase

       

      Thank you.

        • 1. Re: LOD on weeks & YTD
          chun wey teo

          Hi all,

           

          just an update:

          Capture.JPG

          Manage to get the above result but missing one step.  The no of purchase made is calculated since 2015 week 1, thus explain so many purchases.

           

          How do I do a filter or parameter to calculate the no of purchase due to selected parameters.

           

          Had attached the revised workbook (Sheet 7) and appreciate if some one can guide if I am on the right track to reach the objective.

           

          Thank you.


          Rdgs

           

           

          CHun Wey

          • 2. Re: LOD on weeks & YTD
            Ivan Young

            Hi Chun Wey,

            I'm using an older version of Tableau and can't open your workbook but attached is a super store example that allows a parameter to filter on the number of customers by their number of orders per month(orders aren't frequent enough to do by week.  Take a look and let me know if you have any questions.

             

            Regards,

            Ivan

            • 3. Re: LOD on weeks & YTD
              chun wey teo

              Hi Ivan,

               

              thanks for the response.

               

              Opened the workbook, however, not actually what I am looking for in term of filtering.

              Took out the T|F filter to show all the rows.  The filter is filtering on the no of order per month. So if 1 is chosen, only the first row is show.

               

              My filter objective: Example.  Choose a date, June 2011.  The result will show all the customer who have made the nth order from June 2011 till date. If the person has made an order in May 2011 and another order in June 2011.  Without the filter, result would have been May 2011 -> 1 Order, June 2011 -> 2 Order made.  With filter, May 2011 will not be shown and June 2011 will become 1 Order.

               

              Hope that clear up.

               

              Thanks

              Capture.JPG

              • 4. Re: LOD on weeks & YTD
                chun wey teo

                Hi All,

                 

                Any one can help or guide on this?

                Have googled a few forum and articles but cant seem to find a similar solution. 

                 

                Appreciate if someone can help on this.

                 

                Thank you.

                 

                Rdgs

                Chun Wey

                • 5. Re: LOD on weeks & YTD
                  Ivan Young

                  I'm not really clear on what your objective is or what you mean by nth.  Is your issue that your LOD measure is not being affected by the filter?

                  • 6. Re: LOD on weeks & YTD
                    chun wey teo

                    Hi Ivan,

                     

                    Objective: To show the number of purchase the distinct customer made during a period based on filtering.

                     

                    Scenario 1: On week 1, Customer A has made a purchase and it is show as 1st purchase in the first row. In week 35, he has made a purchase and it is show as 2 purchase in second row (from start till now).

                     

                    Scenario 2: On week 35, Customer A has made a purchase and it is shown as 1 in the first row. On Week 37, Customer A has made another purchase and it should show 2 purchase made in the second row. 

                    Note: Time Filter has filtered to week 35, so week 1 purchase in scenario 1 should not shown and the calculation should run based on the min week.

                     

                    I'm not really clear on what your objective is or what you mean by nth.  Is your issue that your LOD measure is not being affected by the filter?

                     

                    nth purchase refers to 1st purchase, 2nd purchase, 3rd purchase, 4th purchase,etc...

                    n here refers to n times.  It can be any number made by the customer.  What I am trying to do here is to find the number of purchase made by each customer in a time period which can be filtered.

                     

                    Is your issue that your LOD measure is not being affected by the filter?

                    Yes, that is part of the issue.  When I filtered, scenario 2 won't happen.  It is still calculating from week 1 instead and not the filter value of week 35.

                     

                    Thank you.

                    • 7. Re: LOD on weeks & YTD
                      Ivan Young

                      Hi Chun wey,

                      I've been out of town so I'm not sure if you resolved your issue.  Try adding your filters to context and they should affect your LOD expressions.

                       

                      Regards,

                      Ivan

                       

                      • 8. Re: LOD on weeks & YTD
                        chun wey teo

                        Hi Ivan,

                         

                        No, it has not been resolved as it seem more complicated as I know.

                         

                        Thank you

                        Rdgs

                        Chun wey

                        • 9. Re: LOD on weeks & YTD
                          chun wey teo

                          Hi,

                           

                          Can anyone help to advise on this or kindly point out how to go about this?

                           

                          Thank you in advance.

                           

                          Rdgs

                          Chun Wey

                          • 10. Re: LOD on weeks & YTD
                            chun wey teo

                            Hi,

                             

                            Anyone guidance would be appreciated.

                             

                            Thanks!

                             

                            Chun Wey