Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
swarup_malli
Specialist
Specialist

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

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

above 1.png

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

Above 2.png

I get nothing after converting the pivot into cross table

See fig below

Above 3.png

I'm attaching a sample app

1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

4 Replies
arthur_dom
Creator III
Creator III

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
Partner - Champion III
Partner - Champion III

2015-06-12 #1.PNG

swarup_malli
Specialist
Specialist
Author

Thank you Guys!

Is it possible to make the result cumulative ?

ex:

Currently looks like this

Cumulative 1.png

Want the result to look like

cumulative 2.png

I'm starting a new thread for this here.

swarup_malli
Specialist
Specialist
Author

Hello ,

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