Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Action-Packed Learning Awaits! QlikWorld 2023. April 17 - 20 in Las Vegas: REGISTER NOW
Showing results for 
Search instead for 
Did you mean: 
Creator II
Creator II


Hi All,

I am having the below requirement.

I am having few gates associated with Actual Dates against multiple programs.

For Example :


13992Gate 107-05-2015
13992Gate 229-07-2015
13992Gate 326-02-2016
13992Gate 422-07-2016
13992Gate 521-11-2016
13992Gate 613-02-2017

Likewise, there are multiple programs each associated with a gate and actual date

My requirement is to find out Average of Gate 6 actual date minus Gate 1 actual date for programs in the quarter

Also, % of programs with duration 6 months or less in the quarter.

Could you please help me with the solutions for this.

I have tried in all the possible ways, but i am unable to find out a solution for this.

Thanks in advance,

Jeshwanth B

6 Replies
Champion III
Champion III

Can you provide some sample data with expected output please? You can use set analysis for calculating average.

Creator II
Creator II

Hi Nagaraju,

Below is the sample data

   PRG_ID, Gate, Actual Date

12363Gate 1-
12363Gate 2-
12363Gate 3-
12363Gate 407-11-2016
12363Gate 527-02-2017
12363Gate 627-03-2017
13050Gate 1-
13050Gate 205-01-2015
13050Gate 305-01-2015
13050Gate 405-01-2015
13050Gate 506-06-2016
13050Gate 607-11-2016
13277Gate 1-
13277Gate 2-
13277Gate 3-
13277Gate 412-12-2016
13277Gate 516-01-2017
13277Gate 606-02-2017
13309Gate 1-
13309Gate 2-
13309Gate 313-07-2015
13309Gate 402-05-2016
13309Gate 510-10-2016
13309Gate 603-04-2017
13356Gate 112-01-2015
13356Gate 209-02-2015
13356Gate 308-09-2015
13356Gate 414-03-2016
13356Gate 525-07-2016
13356Gate 619-12-2016

Expected Output is like

Reported PeriodQ1Q2Q3Q4
Avg Duration14.1814.2613.1314.2
Programs < 6 months duration15%13%20%15%

Please do the needful.


Jeshwanth B


Actual dates for Gate 1 in the given date set are null. So whats your logic when its null? should we consider Min Date for the respective ID as Gate 1- Actual Date

For suppose 7-11-2016 for ID 12363.

Champion III
Champion III

What is the duration calculated in? Like Days or hours ? Some of your Gate 1 dates are null what you want them to be replaced with?

Creator II
Creator II

Duration needs to be in months.

Actual Date Null Values to be replaced with nothing.

we will consider only the values which are not null.


Jeshwanth B

Creator II
Creator II

No, we should not consider any min date.

It should be calculated only when the value is not null.