5 Replies Latest reply on Jan 8, 2019 4:39 AM by Jeanne Sun

    Calculate difference by different dimension

    Jeanne Sun

      Hi guys,

       

      I got some data that looks like the "Raw data" below. What I want to calculate (see "Expected output 1") is for a particular "Fund" (e.g. xyz or lmn) the difference of the "Ongoing cost" between "with distribution cost" and "Clean charge", ignoring "no mgt and distri cost" (i.e. treated then as null).

       

      Raw data:

      Fund Ongoing costShare class fee basis
      xyz0.78Clean charge
      xyz1.43with distribution cost
      lmn 0.69Clean charge
      lmn 1.27with distribution cost
      lmn 0.08no mgt and distri cost

       

      Expected output 1:

      Fund Ongoing costShare class fee basis
      Distribution cost
      xyz0.78Clean charge0.66
      xyz1.43with distribution cost 0.66
      lmn 0.69Clean charge 0.58
      lmn 1.27with distribution cost 0.58
      lmn 0.08no mgt and distri cost null

       

      Many thanks in advance!

        • 1. Re: Calculate difference by different dimension
          Sankar M

          Hello Jeanne,

           

          Please find the attached workbook link.

           

          Tableau Public

           

          Please let me know in case of any issues. Please mark the answer as helpful and correct if it resolves your question.

           

          Thanks!
          Sankar M

          • 2. Re: Calculate difference by different dimension
            Michael Hesser

            Hello Jeanne!

             

            I've attached an example I was able to throw together; you may be able to do this in fewer steps than me.

             

            I started with an LOD expression that pulls the value by Fund:

             

            Dimension Difference

            {fixed [Fund]:sum(iif([Share class fee basis] = "with distribution cost",[Ongoing cost],0))}

            -

            {fixed [Fund]:sum(iif([Share class fee basis] = "Clean charge",[Ongoing cost],0))}

             

            NOTE: This could generate some unusual values these are not present in a fund, or if they are listed more than once. Similarly, you could get a negative number if your Clean charge is > distribution charge, but that's easy to fix!

             

            Next I dropped our dimension [Share class fee basis] into a SET and selected ONLY the two values we're interested in:

            "Why on earth use a set for this?!?"

            Well, the truth of the matter is you don't have to. But sets have some really cool properties that can make programming & debugging a breeze.

            • They're fast to set up! One click and you're ready to filter!
            • You don't have to worry about spelling! The sets take just what's in your data, so you when writing code you don't need to worry about if you spelled something correctly or not. Bazinga!
            • They make checking VERY FAST! Used in calcs or filters, sets work fast to immediately tell you if a value is IN or OUT... you don't need to work your fingers to the bone creating long lists of nested IF statements.

             

            After our set is made, we can combine the two

            This simply checks if the value is one of the two we want. If so, it displays the calculated difference. Otherwise, it displays nothing. NOTE: If you want it to actually display the word NULL rather than a blank, you'll also need to convert your Dimension Difference formula into a string. I went for a short cut.

             

            The last thing I did was playing around with the rows to sort the data as I saw in your picture. I included the [Diff or Null] calc, which I hid, to sort null values to the bottom.

             

            I hope this helps!

             

            --Michael

            • 3. Re: Calculate difference by different dimension
              Jeanne Sun

              Thanks Sankar. It looks right but I cannot download the workbook from the tableau public link.

              • 4. Re: Calculate difference by different dimension
                Sankar M

                Hi Jeanne,

                 

                Look at the top right corner of the view. You will find the option to

                download workbook.

                • 5. Re: Calculate difference by different dimension
                  Jeanne Sun

                  Thanks Michael.

                   

                  The Dimension Difference formula is spot on. I did some changes to make it return null.

                   

                  {FIXED [Fund Standard Name]:AVG(IIF([Share Class Fee Basis]="Mgmt + Dist Fee",[Ongoing Cost],NULL))}

                  -

                  {FIXED [Fund Standard Name]:AVG(IIF([Share Class Fee Basis]="Clean (Mgmt Fee only)",[Ongoing Cost],NULL))}