topic Re: Work around Before ( ) function ? in QlikView

Work around Before ( ) function ?

swarup_malli — Tue, 16 Jun 2015 14:55:54 GMT

Hi,

I'm using Before () function to find the difference between 2 rows for rolling months in a pivot table with horizontal dimensions.

The problem with before() function is ,after using it the first column becomes null.That's how its designed to work.

I'm using the following formula ( subtracting 2 rows to display sum for rolling months ,ROLLING_FLAG={1} is the flag I'm using to display rolling months)

SUM(

AGGR(

(Sum ({<ROLLING_FLAG={1}>}FLIGHT_HRS)

/ COUNT({< ROLLING_FLAG={1}>} AC_SERIAL))

* COUNT({<ROLLING_FLAG={1}>}AC_SERIAL),MODEL_CODE,ROLL ))

Before( total(SUM(

AGGR(

(Sum ( FLIGHT_HRS)

/ COUNT(AC_SERIAL))

* COUNT(AC_SERIAL),MODEL_CODE,ROLL ))

))

I get the following result (see fig below)

I want the column for July-2014 to be populated with the right value .

This is how I want the pivot table to look like ( I'm using different columns in the below table,I want the below values to be shown in the above chart)

I'm attaching a sample qvw for reference.

Message was edited by: swarup malli

Re: Populate the first column of Pivot table ,by manipulating Before () function

Anonymous — Tue, 16 Jun 2015 16:21:39 GMT

why don't you try with if condition with the below logic?

If the min(month) then the normal sum else you can use the code which you have

Re: Manipulate the result of before ( ) function

swarup_malli — Tue, 16 Jun 2015 18:17:48 GMT

I'm sorry I did not get you ? what's normal sum ?

Re: Work around Before ( ) function ?

ramoncova06 — Tue, 16 Jun 2015 19:11:40 GMT

it is as you commented, since there is nothing before, QV does not know what value to use

use dimensionality for this

How to use - Dimensionality()

Re: Work around Before ( ) function ?

swarup_malli — Tue, 16 Jun 2015 19:17:28 GMT

Thank you ! Ramon let me take a look at .

Re: Work around Before ( ) function ?

swarup_malli — Tue, 16 Jun 2015 20:06:07 GMT

I was able to fix it.

Previoulsy the rolling months was for 12 months, that's why I was losing the first month (as a result of using before () )

Now I created a rolling month for 13 months, This way I get all 12 months of data.