Will TOTAL help me with Set Analysis?

[#1]

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

[Miguel_Angel_Baeyens · Accepted solution]

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

[#3]

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.

[#4]

like Miguel Miguel...

=Sum({$<TransDate = {">=$(vDateFYStart)<=$(vDateEnd)"}>} TOTAL <AccountType>  Amount)

[#5]

Hey Pari Pari,

Oh no! I was me all along.

Instead of Company I should have used CompanyDatabase, same field value wrong dimension. I suck!

I really appreiciate you both for your help.

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

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