Usage Aggr(), Dimensionality() and Before() in Total of Pivot Table

[#1]

Hello everybody!

I need help with the calculating expression for Total columns and rows in Pivot Table.

Expression "Cnt" depends on sum(Amount) of previous period for each product :

1) If any product appeares for the first time, cnt=sign(sum(Amount))     (1 or -1)

2) Else if the product appeares one more time, we check if accumulation sum(Amount) for this product before and included current period equal zero, cnt=sign(sum(Amount)).

3) Else cnt=0

My expression calculates right in Nototal cells. But I think it does not see 'before()' correctly in Total.

I show table with products for visualization, but finally I need table without dimension Product, only Structure and Dep (see file .qvw).

Can someone help me out with the right expression for Cnt?

Many thanks.

[swuehl]

Within the Aggr() virtual table, you can't use Before() / After(), only Above() /  Below().

The structure of Aggr() table is like a straight table, not a pivot table.

Also the Aggr() function dimension values might not be in the same sort order that the dimension values in your pivot table chart, but you can check

The sortable Aggr function is finally here!

[#3]

Very pity! Maybe someone can suggest another best solution for this problem without before() in QV 11?

[sunny_talwar]

See if this look right

Sum(Aggr(

    If(

          Rangesum(Above(Aggr(Sum(Amount), Product, YearMonth), 1, RowNo())) = 0 or

            Rangesum(Above( Aggr(Sum(Amount), Product, YearMonth), 0, RowNo())) = 0,

Sum(Sign(Aggr(Sum(Amount), Product, YearMonth))), 0), Product, YearMonth))

[#5]

Sunny T, thanks, but it also does not work correctly.

[sunny_talwar]

Is that the only issue? or can you point some other places you might still be seeing in correct numbers?