3 Replies Latest reply on Apr 11, 2016 3:19 AM by Jonathan Barberi

    Calculating running customers number

    Jonathan Barberi

      Hi all,

       

      I'm trying to calculate the running customers number.

       

      I have two extracts :

      - All contracts

      - Ended contracts

       

      Those two extracts are linked on MY([Contract.StartDate]) VS MY([Ended Contracts.EndDate]) and RecordType (which represent the two products I want to follow)

       

      I have two problems with this method I would like to solve :

      - How can I display "0" if there are no value (for example, I have no new Customers on April 2015 for Sct 2, so no values are displayed)

      I tried several thing like IIF(SUM[New] > 0, SUM[New], 0), or LOOKUP(SUM([New]),0) as described here : Re: Displaying 0 in null fields but it's not working.

      - As I said before, I have no new customers on April 2015 for Sct 2, but I may have some Ended Contracts on April 2015 for this product. Because there is no record on the master extract, nothing is displayed ... Well, I can easily understand why, but how can I do to track the running customer number in that case?

       

      In attachment, the workbook I'm working on

       

      Sorry for my bad english, I'm french and thank you by advance for your help !

        • 1. Re: Calculating running customers number

          Jonathan Drummey any chance you'd be so kind as to offer your wisdom?

          • 2. Re: Calculating running customers number
            Jonathan Drummey

            Thanks for the ping, @Diego!

             

            Hi Jonathan,

             

            It's a bug in Tableau v9.0 that changed Tableau's densification behaviors (of which Show Missing Values is one), I know it's fixed as of Tableau v9.2.6. If you upgrade it'll be fixed. If you can't upgrade then you can put an INDEX() table calculation onto the Level of Detail Shelf and set the Compute Using to Table (Down). This triggers domain completion densification and gets similar results, I've attached a v9.0 workbook with INDEX() added as an ad hoc calculation.

             

            A way to more fully avoid this problem is to use a third data source that has all of the dimensionality you need, i.e. every combination of date and Nom du type d'enregistrement (groupe), you would use that source as the primary source and then blend in both the Contracts and Ended contracts sources.

             

            Jonathan

            • 3. Re: Calculating running customers number
              Jonathan Barberi

              Hi Jonathan Drummey,

               

              Thank you for your answer. And sorry I didn't responded before.

               

              I took some time this morning to test the different ways you gave me.

               

              First, I updated Tableau Desktop to v.9.3, and I'm still missing some ended contracts because of the inner join on "date de début" from my primary data source. No data on the primary data source => no date on the secondary data source.

               

              I tested the solution with a third data source, but the problem remain the same as I used the field "date de début" as a column so the inner join doesn't work.

               

              I attached another sample dashboard with two sheets.

               

              The first sheets show the number of customer without densification.

               

              The second one is the same by sector & activity.

               

              The total is not the same, because I'm missing ended contracts in the second sheet ...