5 Replies Latest reply on Jul 2, 2017 6:53 AM by Zhouyi Zhang

    LOD Count & YTD formula

    Michael Dvorak

      I have created a formula which counts distinct number of items we sold. We have at invoice number some 0 which was creating a count , I was able to exclude these and the following formula works well :

      { include [cust id],[sku]: IF sum([delv])=0 THEN 0 ELSE COUNTD( sku]) END}

       

      Now I am trying to make this work for YTD - the following works but does not exclude the 0s and I am struggling to include IF sum([delv])=0 THEN 0

       

      { include [cust id],[sku]: COUNTD( IF [CY YTD] THEN [sku] END)}

       

      As a newbie any gidance would be appreciated.

       

      Thanks

      MD

        • 1. Re: LOD Count & YTD formula
          Zhouyi Zhang

          Hi, Michael

           

          have you tried

           

          { include [cust id],[sku]: COUNTD( IF [CY YTD] and [delv] <> 0 THEN [sku] END)}

           

          ZZ

          • 2. Re: LOD Count & YTD formula
            Michael Dvorak

            Thanks ZZ !

             

            No I have not tried it and Yes that worked for me .

            But not 100% because of LOD thought.

            I have a a follow up question - I am not sure should open a new ticket.

             

            I have modified the formula to be >0 rather then <> to suit our needs.

            { include [cust id],[sku]: COUNTD( IF [CY YTD] and [delv] > 0 THEN [sku] END)}

             

            Each invoice has a code for sale and /or pickup - so we can sell 1 and pickup -1 = net 0 

            My new YTD formula picks this counts this as  1 - since we did initially sell it .

            The original formula without the YTD component excludes this as I have the sum included

            IF sum([delv])=0 THEN 0 ELSE COUNTD( sku]) END}

             

            I can not include Sum into the new YTD formula (better said don't know how) due to aggregates.

             

            Thanks again

            • 3. Re: LOD Count & YTD formula
              Zhouyi Zhang

              Hi, Michael

               

              I am not quite getting what you described. If there is a sample workbook and tell me what's your expected value would be helpful

               

              ZZ

              • 4. Re: LOD Count & YTD formula
                Michael Dvorak

                I will try to explain with example :

                1/ We sell a item to a customer which creates invoice - no issue here.

                 

                2/ When we return we find that the quality of the product has diminished so we replace the product

                Invoice contains the following descriptions, I am using + to separate the fields

                In summary we a line for Sale as +1 and Pickup -1 The net QTY sold is 0.00

                Inv No + SKU      +  Inv. Action  +  QTY

                123     + Item1     + Sale            +    1

                123     + Item1     + Pickup         +   (1)

                The following formula shows the correct amount = 0

                POD= { INCLUDE  [Cust Id],[SD sku]: IF sum([QTY])>0 THEN COUNTD([SD sku]) ELSE 0 END}

                I believe that  this is due to the inclusion of sum([delv])

                 

                3/ The following formula shows up as 1

                POD YTD ={ INCLUDE  [Cust Id],[SD sku]: COUNTD(if [CY YTD] and [QTY]> 0 THEN [SD sku]END)}

                 

                When replying I was unable to attache workbook , I thus open a new topic - Count & LOD

                https://community.tableau.com/message/635546#635546

                 

                 

                Hopefully that helps

                • 5. Re: LOD Count & YTD formula
                  Zhouyi Zhang

                  Hi, Michael

                   

                  Sorry about the late response, and thanks for your detailed explanation. It is much more clear to me.

                   

                  Please find my solution below to your question, hope this could help.

                   

                   

                  Workbook attached for your reference. Please let me know if any further question

                   

                  ZZ