topic Re: Above ( ) function which is smiliar to lag function used in sql not giving desired result ? in QlikView

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

swarup_malli — Thu, 11 Jun 2015 19:00:52 GMT

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

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

petter — Thu, 11 Jun 2015 19:04:09 GMT

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

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

petter — Thu, 11 Jun 2015 19:08:11 GMT

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

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

antoniotiman — Thu, 11 Jun 2015 19:11:05 GMT

Hi,

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

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

Regards,

Antonio

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

swarup_malli — Thu, 11 Jun 2015 19:15:59 GMT

You are the man !

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

swarup_malli — Thu, 11 Jun 2015 19:59:33 GMT

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

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

petter — Thu, 11 Jun 2015 20:04:02 GMT

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

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

swarup_malli — Thu, 11 Jun 2015 20:09:29 GMT

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.