Aggregate Challenge

[marcel_olmo]

Hi guys, I have one table with two fields "total old" and "total new", and I want to turn "Total Old" in "Total new":

Here's my current table :

And this is my desired result :

What I'm trying to do is if I have four rows like this :

Date	Num
20/02/2013	1
20/03/2013	2
20/04/2013	1
20/05/2013	4
20/06/2013	6

Get a table like this (with more fields related ) :

Date	Num
20/02/2013	0
20/03/2013	0
20/04/2013	0
20/05/2013	0
20/06/2013	14

This is my current formula, but it doesn't work :

If(

    MaxString(Years)

    <>

    below(Total MaxString(Years))      

    or       

    MaxString( [Km Group] )

    <>

    below(Total MaxString( [Km Group]  ))

    ,      

    Sum(Total<Years, [Km Group]   > [Total Old] ),' ')

Here I attach my qv document.

Regards, Marcel.

[Oleg_Troyansky]

Marcel,

I compared your document with the Dunphy document, and I think I understand why one document works and another doesn't...

In the working document, the pivot table has 2 dimensions and the expression calculates the total for one of the dimensions. Hence, visually it shows zeros for all the lines except for the last line, where it calculates the overall total.

In your document, however, you have 2 dimensions, and your expression is comparing both dimensions and calculates the total for both dimensions. So, in a way you duplicate the way pivot table works by default:

//sum(  [Total valor recl. Marca]  )

    If(

    MaxString(Years)

    <>

    below(Total MaxString(Years))

    or

    MaxString( [Km Group] )

    <>

    below(Total MaxString( [Km Group]  ))

    ,

    Sum(Total<Years, [Km Group]   > [Total Old] ),' ')

The IF condition will always render true because each row will hold a unique combination of Years and Km Group, and the formula will always calculate the regular sum, because the Total <> has both Dimensions in it - Years and Km Group.

In order to get the effect similar to Dunfy's, I'd think you want to do something like this:

    If(

    MaxString(Years) <> below(Total MaxString(Years))

    ,

    Sum(Total <Years> [Total Old] ),' ')

This should work, unless I misunderstood your requirement.

cheers,

Oleg Troyansky

www.masterssummit.com

/*

    If(

    MaxString(Customer)

    <>

    below(Total MaxString(Customer))

    or

    MaxString([Item Subgroup])

    <>

    below(Total MaxString([Item Subgroup]))

    ,

    Sum(Total<Customer,[Item Subgroup]> [Total Stock Wgt]),' ')

*/

[marcel_olmo]

THanks Oleg for your try, I know what you mean, but the point is that I need to get the same result as "desired result" (the table with green colour), and if I do the total only for 1 dimension, I won't get a total by year and kilometer group.

Regards, Marcel.