topic Re: Calculato in QlikView

Calculato

Jesh19 — Mon, 17 Jul 2017 16:59:03 GMT

Hi All,

I am having the below requirement.

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

For Example :

PRG_ID	DESCRIPTION_GATE	ACTUAL_DATE_FORMAT
13992	Gate 1	07-05-2015
13992	Gate 2	29-07-2015
13992	Gate 3	26-02-2016
13992	Gate 4	22-07-2016
13992	Gate 5	21-11-2016
13992	Gate 6	13-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

Re: Calculato

vishsaggi — Mon, 17 Jul 2017 17:09:32 GMT

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

Re: Calculato

Jesh19 — Mon, 17 Jul 2017 17:25:41 GMT

Hi Nagaraju,

Below is the sample data

PRG_ID, Gate, Actual Date

12363	Gate 1	-
12363	Gate 2	-
12363	Gate 3	-
12363	Gate 4	07-11-2016
12363	Gate 5	27-02-2017
12363	Gate 6	27-03-2017
13050	Gate 1	-
13050	Gate 2	05-01-2015
13050	Gate 3	05-01-2015
13050	Gate 4	05-01-2015
13050	Gate 5	06-06-2016
13050	Gate 6	07-11-2016
13277	Gate 1	-
13277	Gate 2	-
13277	Gate 3	-
13277	Gate 4	12-12-2016
13277	Gate 5	16-01-2017
13277	Gate 6	06-02-2017
13309	Gate 1	-
13309	Gate 2	-
13309	Gate 3	13-07-2015
13309	Gate 4	02-05-2016
13309	Gate 5	10-10-2016
13309	Gate 6	03-04-2017
13356	Gate 1	12-01-2015
13356	Gate 2	09-02-2015
13356	Gate 3	08-09-2015
13356	Gate 4	14-03-2016
13356	Gate 5	25-07-2016
13356	Gate 6	19-12-2016

Expected Output is like

Reported Period	Q1	Q2	Q3	Q4
Avg Duration	14.18	14.26	13.13	14.2
Programs < 6 months duration	15%	13%	20%	15%

Please do the needful.

Thanks,

Jeshwanth B

Re: Calculato

aarkay29 — Mon, 17 Jul 2017 18:25:20 GMT

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.

Re: Calculato

vishsaggi — Mon, 17 Jul 2017 18:25:56 GMT

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?

Re: Calculato

Jesh19 — Tue, 18 Jul 2017 06:09:39 GMT

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.

Regards,

Jeshwanth B

Re: Calculato

Jesh19 — Tue, 18 Jul 2017 06:10:35 GMT

No, we should not consider any min date.

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