3 Replies Latest reply: Aug 8, 2017 11:31 AM by Alison Cooke RSS

    Quarterly Variance Column

    Alison Cooke

      Hello Everyone,

       

      I have a table that I would like to look like this:

      Screenshot (81).png

       

      Where, for each quarter, the Actual, Budget and Variance is displayed.

      The Actual and Budget column are working correctly, I am just having difficulty with the Variance column.

       

      For it I have the following expression:

       

      Screenshot (82).png

       

      It is working for some columns but not for other.

      Please can someone help me get it to work.


      Thank you all for your support,

      Alison

        • Re: Quarterly Variance Column
          bruno bertels

          Hi

           

          May be this somethink like this ?

           

          If(Quarter='Q1',mesure for Q1,

          if (Quater = Qé, mesure for Q2 etc ...

           

          And Or what about a 3rd mesure :

          column(2) - column(1)

           

          Hope it helps ..

          But pretty not sure

           

          Bruno

          • Re: Quarterly Variance Column
            Alison Cooke

            There issue was 'distinct' found before the second 'Project Expenditure Amount.

            So, instead of this:

             

               if (sum ({$<[Quarter] = {"Q1"}>} distinct [Project Expenditure Amount]), (sum (distinct [Q2 Project Budget])) -

              sum ({$<[Quarter] = {"Q1"}>} distinct [Project Expenditure Amount]),

            [...]

             

            It needed to be this:

             

               if (sum ({$<[Quarter] = {"Q1"}>} distinct [Project Expenditure Amount]), (sum (distinct [Q2 Project Budget])) -

              sum ({$<[Quarter] = {"Q1"}>} distinct [Project Expenditure Amount]),

            [...]

             

            Distinct meant that it wasn't counting identical figures and hence why it was working for some items and not others.

            • Re: Quarterly Variance Column
              Alison Cooke

              Sorry, I meant that it should look like this: 

               

              if (sum ({$<[Quarter] = {"Q1"}>} distinct [Project Expenditure Amount]), (sum (distinct [Q2 Project Budget])) -

                sum ({$<[Quarter] = {"Q1"}>} = [Project Expenditure Amount]),

              [...]

               

              That's the solution.