1 Reply Latest reply on Dec 29, 2016 9:11 AM by Joe Oppelt

    Cross tab column alignment

    Suresh Babu Kommuri

      Hello All,

       

      I have struggle in implementing below requirement. Could you please anybody help me.

       

      I have below dimensions

      Wholesaler

      Date

      Customer ID

      Region

       

      Measures

      Total Customers:     CountD(Customer ID)

      Total IND Customers:         CountD(IFF([Region]='IND',[Customer ID],NULL))

      Total UK Customers:          CountD(IFF([Region]='UK',[Customer ID],NULL))

      %IND Customers:     I need to show [Total IND Customers] / [current month IND customers] --- Need help on this formula

      %UK Customers:     I need to show [Total IND Customers] / [current month UK customers] --- Need help on this formula

       

      I need to show data in this fashion but I am not able to achieve this. Kindly help me in this requirement. and also help me in Grand totals.

       

      10/201611/201612/201610/2016
      11/201612/2016
      WholesalersTotal CustomersTotal IND CustomersTotal UK Customers%IND Customers%UK CustomersIND CustomersIND CustomersIND CustomersUK CustomersUK CustomersUK Customers
      Tesco100002000800020%80%150019002000750065008000
      Walmart2000070001300030%70%50006000700010001000013000
      Grand Totals3000090002100065007900900085001650021000

       

      Advanced Thanks

       

      Suresh

        • 1. Re: Cross tab column alignment
          Joe Oppelt

          Tableau won't let you insert things in the middle of a crosstab like Excel will.  the two packages are structured differently.  Tableau is more dimensional-grid oriented.

           

          You can make the calcs you need % IND/UK numbers.  Use LODs to get the total sum of IND and total sum UK.  The specific syntax will depend on things you have set up in your sheet so I can't give you an exact calc, but I bet it will look something like this:

           

           

           

          { EXCLUDE [month] : COUNTD( IF [Region] = "IND" then [Customer ID] END ) }

           

          Essentially this says to get the countD regardless of month.

           

          Your [Total IND Customers] that you showed above will get you the COUNTD by month (if you have months on the columns shelf.)

           

          Then you can do [your LOD Calc] /  [Total IND Customers]

          and that will give you the % you are looking for.

           

          How, exactly, you will display that is your next step.

           

          Can you upload a packaged workbook and your tableau version?  We can play with it.