topic Re: Will TOTAL help me with Set Analysis? in QlikView

Will TOTAL help me with Set Analysis?

Wed, 25 Jul 2012 09:52:45 GMT

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 — Wed, 25 Jul 2012 10:00:57 GMT

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?

Thu, 26 Jul 2012 08:25:16 GMT

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?

Thu, 26 Jul 2012 08:44:34 GMT

like Miguel Miguel...

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

Re: Will TOTAL help me with Set Analysis?

Fri, 27 Jul 2012 00:27:20 GMT

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)