Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
swarup_malli
Valued Contributor

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

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

2015-06-12 #1.PNG

4 Replies
arthur_dom
Contributor III

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

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

)

MVP
MVP

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

2015-06-12 #1.PNG

swarup_malli
Valued Contributor

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

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
Valued Contributor

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

Hello ,

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