Above ( ) function which is similar to lag function used in sql not giving desired result ?

[swarup_malli]

Hi,

In the expression I'm trying to find the difference between 2 rows .

Ex  : Raw data

Year    Month   Sum

2012    Dec      100

2013    Jan       200

2013    Feb       300

2013    March   400

2013    April      700

I'm trying to get this result by subtracting the value in the value present in the row above from the row below ( see below for ex)

The result should look like ,

2013 Jan    100

2013  Feb    100

2013  March 100

2014  April     100

I tried using above() function, for some reason the result does not contain the first month of that year ..in my case the month of jan disappears from  the result .Any ideas ?

I'm attaching a sample qvw

[petter]

You have two dimensions Year and Month ... Above() at the the first row of a new year returns Null and the difference between Null and January is Null...

[petter · Accepted solution]

So Above(TOTAL Amount) will be the right for you...

[antoniotiman]

Hi,

If(RowNo() = 1,Sum(..),

Sum(.. )-Above(Sum(..))

Regards,

Antonio

[swarup_malli]

You are the man !

[swarup_malli]

But when I use set analysis

ex:

(Sum ({<REPORT_YEAR={2014}>}FLIGHT_HRS)

/ COUNT({<REPORT_YEAR={2014}>} AC_SERIAL)

* COUNT({<REPORT_YEAR={2014}>} AC_SERIAL)

)

-

above( total(Sum ({<REPORT_YEAR={2014}>}FLIGHT_HRS)

/ COUNT({<REPORT_YEAR={2014}>} AC_SERIAL) )

* COUNT({<REPORT_YEAR={2014}>} AC_SERIAL))

I again lose the month of jan because of this condition REPORT_YEAR={2014}

How can I include Jan of 2014 this way I can get all 12 months

[petter]

If you restrict the underlying rows to be only in 2014 you can't really get hold of December 2013. So then you have to include the December value from the previous year but make sure that you don't include it in any other expressions except the one with Above() in it. Then December 2013 shouldn't come up as a row as you have the chart eliminating rows with nulls (that is the standard for all charts at least).

[swarup_malli]

Petter,

That makes sense, let me start a new thread with a sample qvw, because when I convert the pivot into a cross table (by dragging the dim upwards) I again get dashes '-' instead of valid data.

I'm pasting the new thread here.