Way around Above ( ) function ?similar to Lag function in SQL or oracle

[swarup_malli]

I'm using above function to subtract upper row from the lower .It works fine when I use 2 dimensions ( year and  month ),because it displays all the years and months.See fig below

My requirement is ,I want to show values for a particular year, which I have done using set analysis.The problem is when I use set analysis

See below:

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},because I'm excluding Dec 2013,thus Dec 2013 is null .And Null minus null is null

See fig below

I get nothing after converting the pivot into cross table

See fig below

I'm attaching a sample app

[arthur_dom]

Man... i used the following, attached is  the qvw.

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

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

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

)

-

if(rowno()=1,

     //previous

     (Sum (TOTAL{<MONTH_NAME={'dec'}, REPORT_YEAR={"<2014"}>}FLIGHT_HRS)

     / COUNT(TOTAL{<MONTH_NAME={'dec'},REPORT_YEAR={"<2014"}>} AC_SERIAL)

     * COUNT(TOTAL{<MONTH_NAME={'dec'},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))

)



[petter · Accepted solution]

[swarup_malli]

Thank you Guys!

Is it possible to make the result cumulative ?

ex:

Currently looks like this

Want the result to look like

I'm starting a new thread for this here.

[swarup_malli]

Hello ,

I'm trying to implement before () in one more chart ,if you guys are interested you can find it here