Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
swarup_malli
Specialist
Specialist

Work around Before ( ) function ?

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)

QV before.png

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)

QV before  2.png

I'm attaching a sample qvw for reference.

Message was edited by: swarup malli

1 Solution

Accepted Solutions
swarup_malli
Specialist
Specialist
Author

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.

View solution in original post

5 Replies
Anonymous
Not applicable

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

swarup_malli
Specialist
Specialist
Author

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

ramoncova06
Partner - Specialist III
Partner - Specialist III

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

swarup_malli
Specialist
Specialist
Author

Thank you ! Ramon let me take a look at .

swarup_malli
Specialist
Specialist
Author

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.