Work around Before ( ) function ?

[swarup_malli]

Hi,

I'm using Before () function to find the difference between 2 rows   for rolling months in a pivot table with horizontal dimensions.

The problem with before() function is ,after using it the first column becomes null.That's how its designed to work.

I'm using the following formula ( subtracting 2 rows to display sum for rolling months ,ROLLING_FLAG={1} is the flag I'm using to display rolling months)

=

SUM(

     AGGR(

           (Sum ({<ROLLING_FLAG={1}>}FLIGHT_HRS)

/ COUNT({< ROLLING_FLAG={1}>} AC_SERIAL))

* COUNT({<ROLLING_FLAG={1}>}AC_SERIAL),MODEL_CODE,ROLL ))

     -

Before( total(SUM(

     AGGR(

           (Sum ( FLIGHT_HRS)

/ COUNT(AC_SERIAL))

* COUNT(AC_SERIAL),MODEL_CODE,ROLL ))

))

I get the following result (see fig below)

I want the column for July-2014 to be populated with the right value .

This is how I want the pivot table to look like ( I'm using different columns in the below table,I want the below values to be shown in the above chart)

I'm attaching a sample qvw for reference.

Message was edited by: swarup malli

[Anonymous]

why don't you try with if condition with the below logic?

If the min(month) then the normal sum else you can use the code which you have

[swarup_malli]

I'm sorry I did not get you ? what's normal sum ?

[ramoncova06]

it is as you commented, since there is nothing before, QV does not know what value to use

use dimensionality for this

How to use - Dimensionality()

[swarup_malli]

Thank you ! Ramon let me take a look at .

[swarup_malli · Accepted solution]

I was able to fix it.

Previoulsy the rolling months was for 12 months, that's why I was losing the first month (as a result of using before () )

Now I created a rolling month for 13 months, This way I get all 12 months of data.