4 Replies Latest reply: Jul 26, 2012 8:27 PM by Niels van Diermen RSS

    Will TOTAL help me with Set Analysis?

      Hi There,

       

      Can I use Set Analysis with TOTAL?

       

      This is basically what I want:

       

      =Sum(TOTAL <Company, AccountType>  Amount)

       

      However I need the Amount to be restricted by date range.

       

      Sum ({$<PostingDate = {">=$(vDateFYStart)<=$(vDateEnd)"}>} Amount)

       

       

      Is this possible?

       

      The real thing I'm trying to achieve is a reasonbly complex pivot chart comparing previous financial periods to this one...

       

       

      ADDITIONAL READING:

       

       

      I've created and attached a simple qvw file that might make it easier to look at?

       

      Basically want to:

       

      1. SUM at the "Account Type" level

      2. Make that total amount available at the account Level.

      3. The Total Amount must also fit into a variable date range. - This is the hard bit.

      4. This pivot table has a lot of other data in it and is a comparison between prior months and years.

       

      Company

           AccountType

                Account Level Sum(Amount)

       

       

      e.g. Data

       

      Company1

           Cost Of Sales

                     Acc1 $500

                     Acc2 $200

           Income

                     Acc4 $80

                     Acc5 $2000

      Company2

           Cost Of Sales

                     Acc1 $100

                     Acc2 $200

           Income

                     Acc4 $300

                     Acc5 $400

       

      What I want to do is get that total of the AccountType for each Account Level by the Company.

       

      e.g.

       

      Company1

           Cost Of Sales

                     Acc1 $500 - $700

                     Acc2 $200 - $700

           Income

                     Acc4 $80 - $2080

                     Acc5 $2000 - $2080

      Company2

           Cost Of Sales

                     Acc1 $100 - $300

                     Acc2 $200 - $300

           Income

                     Acc4 $300 - $700

                     Acc5 $400 - $700

        • Re: Will TOTAL help me with Set Analysis?
          Miguel Angel Baeyens de Arce

          Hi,

           

          This expression combining both set analysis for date ranges and TOTAL should work:

           

          Sum ({$<PostingDate = {">=$(vDateFYStart)<=$(vDateEnd)"}>} Amount) - Sum({$<PostingDate = {">=$(vDateFYStart)<=$(vDateEnd)"}>} TOTAL <Company, AccountType>  Amount)
          

           

          Hope that helps.

           

          Miguel

            • Re: Will TOTAL help me with Set Analysis?

              Thanks Miguel!

               

              I reckon you've pretty much answered my question but it doesn't quite work as expected for me.

               

              When displaying this part of the calcuation in my pivot chart I get the totals for both companies.

               

              I'm wondering if there is something I can tweek with this formula to get what I want.

               

              Sum({$<PostingDate = {">=$(vDateFYStart)<=$(vDateEnd)"}>} TOTAL <Company, AccountType>  Amount)

               

              Basically I want this cacluation to be limited by the Company dimension for that section of the Pivot Chart. For some reason it returns the total of both companies. Can I make it so the expression is only adding up the total of that account type for that company?

               

              See attached picture.(At this level theBridge highlighted totals should equal each other and the Hahn totals should equal each other. Then when I drill down the total should be the same on everyline of the drill down.

               

              ExamplePivot.png