1 2 Previous Next 29 Replies Latest reply on Oct 1, 2016 1:48 AM by Ivan Young Go to original post
      • 15. Re: new business vs recurring
        Luciano Vasconcelos

        I'm not sure.

         

        See this screen:

         

         

        This is on Sheet 2.

         

        Yours numbers seems a little bit different to me.

        Can you validate them?

        You can try to remove Client, IdRevenue and see what happens.

        • 16. Re: new business vs recurring
          TVTY BI

          Hello Luciano,

           

          I looked and I am not sure this are the right numbers but I understood the ideas (put dimensions and measures in the vue).

          Maybe this is impossible in Tableau that's why I realized a python script to preprocess datas and finally It works

           

          Once again,

          thank you for your help,

          Christophe

          • 17. Re: new business vs recurring
            Luciano Vasconcelos

            David Li, can you help on this one?

            • 18. Re: new business vs recurring
              David Li

              Hi Luciano Vasconcelos! This looks like a pretty tough problem to solve. I'll think about it, but I'm a bit busy at the moment, so I won't be able to dive into it. Sorry!

               

              Off the top of my head, though, you'd probably have to start with the date field (with include missing dates enabled) on columns and the Idrevenue on rows. Then, you'd have to use LOD calculations to make some kind of Boolean toggle for whether or not there was any revenue in the 3 months prior. Probably using EXCLUDE [Idrevenue]. Then, you'd also have to have some kind of Idrevenue-specific calculation that looks backward and figures out if it should be marked as recurring. Then, you'd use a WINDOW_SUM on all of it and hide all but one row using LAST() = 0.

              • 19. Re: new business vs recurring
                Luciano Vasconcelos

                I remembered you because i remembered one you solved.

                • 20. Re: new business vs recurring
                  Ivan Young

                  Hi Cristophe,

                  Are you using a SQL datasource?  If so, with a custom view I think you can get your desired output.  This won't work using Custom SQL on an Excel datasource.  Let me know and I'll explain the process.

                   

                  Regards,

                  Ivan

                   

                   

                  • 21. Re: new business vs recurring
                    TVTY BI

                    Hi Ivan,

                     

                    Sadly, data not come from sql database but Salesforce.

                    Do you think it's possible with salesforce?

                     

                    Regards,

                    Christophe

                    • 22. Re: new business vs recurring
                      Ivan Young

                      Hi Christophe,

                      I'm not at all familiar with Salesforce so I really couldn't say.  I'm still trying to get it to work without transforming the data but haven't had much luck.  It looks like the data may need to be transformed to perform your analysis.

                       

                      Regards,

                      Ivan

                      • 23. Re: new business vs recurring
                        Ivan Young

                        Hi Christophe,

                        I did manage to generate the same output by self joining the datasource on client and creating a few LODs and filters.  Take a look at the attached, data source first, and see if you can get it to work for you.  I will post an explanation when I have a bit more time, the Master worksheet kind of shows the logic flow.  It was a pretty tricky problem to solve without SQL.  Let me know if you have any questions.

                         

                        Regards,

                        Ivan

                         

                        1 of 1 people found this helpful
                        • 24. Re: new business vs recurring
                          Ivan Young

                          I noticed a duplicate for each client causing duplication of Client 1 for July and Client 2 for Aug.  I've updated the workbook to remove them.

                          • 25. Re: new business vs recurring
                            Ivan Young

                            I've attached another workbook with less retarded filters but the output is the same.  Below are the steps I took and an explanation of my thoughts.  The steps will follow Tableau's order of operations as they are important in getting the correct output.

                             

                            1.  In the datasource tab do a self join on Client. This will give all the date combinations on a single row. (Yes it's a lot of data duplication.)

                             

                            2. Create a calculated field to null out the dates from Feuil11, we'll call it New Running Date Fui111: IF [Running Date] = [Running Date (Feuil11)] THEN NULL ELSE [Running Date (Feuil11)] END (This was the step I was missing that made everything come together.)

                             

                            Before Filter

                             

                            3.  Create a context filter to exclude all records where the Running Date > New Running Date Fui111 or New Running Date Fui111 is Null.  (This will allow us to create the necessary LODs).  I called this field Running Date > Previous Running Date: [Running Date] > [New Running Date Fui111] or ISNULL([New Running Date Fui111])

                             

                            After Filter

                             

                            4.  Create an LOD to get the Max New Running Date Fui111 for each IDRevenue, Month.  I called it Max New Running Date FUI111: { FIXED [Running Date], [Idrevenue] : MAX([New Running Date Fui111]) }

                             

                             

                            5.  Calculate the datediff between Running Date and Max New Running Date FUI111.  DateDiff: DATEDIFF('month',[Max New Running Date FUI111], [Running Date])

                             

                             

                            6. Define New or Recurring at the row level:  New or Recurring: IF ISNULL([Date Diff]) THEN 'New' ELSEIF [Date Diff] <= 3 THEN 'Recurring' Else 'New' END

                             

                            6.  Then we need to find the first New or Recurring Value for each ID Revenue.  First New or Recurring by IDRev: IF { FIXED [Idrevenue] : MIN([Running Date]) } = [Running Date] THEN [New or Recurring] ELSE NULL END.

                             

                            7.  Then we create a field to apply First New or Recurring by IDRev to all associated IDRevenues.  Final New or Recurring: { FIXED [Idrevenue] : MAX([First New or Recurring by IDRev]) }.  We now have the correct New or Recurring value for each ID.

                             

                             

                            8. We now need to create a normal filter to remove all the duplicates.  now we want to match back on IDRevenue, Revenue (K€) and RunningDate.

                            Match to Original Record:

                            [Idrevenue] = [Idrevenue (Feuil11)] AND

                            [Revenue (K€)] = [Revenue (K€) (Feuil11)] AND

                            [Running Date] = [Running Date (Feuil11)]

                             

                            Now we are back to our original 29 records :-)

                             

                            9.  Now we just need to layout as desired.

                             

                            Let me know if you have any questions.

                             

                            Regards,

                            Ivan

                            1 of 1 people found this helpful
                            • 26. Re: new business vs recurring
                              TVTY BI

                              Thanks a lot Ivan and everyone, indeed that was a pretty hard analysis. I will use my week end to understand in details your calculations .

                               

                              In one of your old post, you said:

                              "It was a pretty tricky problem to solve without SQL". Is it much more simple if data are

                              in a sql database ?

                               

                              Regards,

                              Christophe

                               

                              • 27. Re: new business vs recurring
                                Luciano Vasconcelos

                                Chris, it should be.

                                I confess i got lost with Ivan's amazing crazy solution.

                                • 28. Re: new business vs recurring
                                  Ivan Young

                                  Hey Christophe,

                                  It is much, much simpler if you transform the data with SQL and it's probably more than a little tricky using Tableau :-).

                                   

                                  Using the SQL in Kettan's post would give you a dataset that would be easy to work with.  Unfortunately since Kettan's twbx is using the legacy connector LOD does not seem to be available.   I created a new excel dataset based on what Kettan's SQL would return.

                                   

                                  Take a look at the attached, I think you'll be able to follow it without much explanation.  The Master worksheet has all the calculated fields used and is intended to show the logic flow.  From a performance standpoint it would be better if you could transform to the output used in the file but I know it might not be a viable option.  Let me know if you have any questions.

                                   

                                  Regards,

                                  Ivan

                                  • 29. Re: new business vs recurring
                                    Ivan Young

                                    Hi Luciano and Christophe, Check out this optimized version.

                                    Tableau Public

                                     

                                     

                                    1. Create a custom date Running Date (Months)

                                     

                                    2. Create a calculated field Previous Running Date: { FIXED [Client], [Running Date (Months)]  : MAX(IF   [Running Date] > [Running Date (Feuil1$1)]  THEN [Running Date (Feuil1$1)] END)}

                                     

                                    3. Create New v Recurring by Running Date: IF ISNULL([Previous Running Date]) THEN 'New' ELSEIF DATEDIFF('month',[Previous Running Date],[Running Date]) <= 3 THEN 'Recurring' ELSE 'New' END

                                     

                                    4. Create New V Recurring by IDRevenue: { FIXED [Idrevenue] : MIN([New v Recurring by Running Date]) }

                                     

                                    5. Create Match to Original: [Idrevenue] = [Idrevenue (Feuil1$1)] and [Running Date] = [Running Date (Feuil1$1)] and [Revenue (K€)] = [Revenue (K€) (Feuil1$1)]

                                     

                                     

                                    Not simple but not nearly as complex as my first solution.

                                     

                                    Regards,

                                    Ivan

                                    1 of 1 people found this helpful
                                    1 2 Previous Next