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

# 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...

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?

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.

• ###### Re: Will TOTAL help me with Set Analysis?

like Miguel Miguel...

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

• ###### Re: Will TOTAL help me with Set Analysis?

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)