Displaying min and max value for multiple dimensions in pivot table

[kevinduck]

Hi,

I'm sure this has been done before, but I'm new to QV and probably don't know the correct term to search...

I have a pivot table as shown below with

dimensions:  Supervisor, Employee, Date

expressions:

     Starting Balance = sum(BeginningOpenItems)

     Inbound = sum(InboundItems)

     Outbound =sum(OutboundItems)

     Ending Balance = sum(EndingOpenItems)

I am having problems getting Starting Balance and Ending Balance displaying the way I want at the Employee and Supervisor dimension (and grand total, although that is not shown).

For the Employee dimension, the Starting Balance should be the balance as of the first date for that Employee under that Supervisor.  For the Supervisor, the Starting Balance should be the sum of all employee Starting Balance as of the first date.

Examples:

For Anna, I want the Starting Balance to total 98 (24 from Mike and 74 from Nancy on 8/27/2014).

For Mike reporting to Anna, I want the Starting Balance to total 24 (Mike's total Starting Balance on 8/27).

For Mike reporting to Bruce, I want the Starting Balance to total 20 (Mike's total Starting Balance on 8/30).

Any help would be greatly appreciated.

Thanks!

Kevin

[sushil353]

Hi,

Try some like:

sum(Total<Supervisor> {<Date = {'$(=Min(Date))'}>}BeginningOpenItems)

For better resolution post the sample data.

HTH

Sushil

[kevinduck]

Thank you, Sushil.  That got me closer, once I figured out how to use the dimensionality() function to sum at the correct level as follows:

=if(Dimensionality()=3,

     sum(BeginningOpenItems),

     if(Dimensionality()=2,

          sum(Total<Employee> {<Date={'$(=Min(Date))'}>}BeginningOpenItems),

                if(Dimensionality()=1,

                    sum(Total<Supervisor> {<Date={'$(=Min(Date))'}>}BeginningOpenItems),0)))

However, it is using the minimum date across all selected dates--I need to use the minimum date for the combination of Employee and Supervisor.  For example, below, Mike's starting balance when reporting to Bruce should be 20 (from 8/30/2014), not 24 (from 8/27/2014).

Any ideas?