topic Re: Way around Above ( ) function ?similar to Lag function in SQL or oracle in QlikView

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

swarup_malli — Thu, 11 Jun 2015 21:18:41 GMT

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

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

arthur_dom — Thu, 11 Jun 2015 21:55:51 GMT

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))

)



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

petter — Thu, 11 Jun 2015 22:16:50 GMT

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

swarup_malli — Fri, 12 Jun 2015 17:01:39 GMT

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.

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

swarup_malli — Tue, 16 Jun 2015 14:58:09 GMT

Hello ,

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