topic Re: Expression for Calculating 1st occurrence date in QlikView

Expression for Calculating 1st occurrence date

Thu, 08 Jan 2015 06:03:43 GMT

Hi All,

what is expression for calculating no of days between 2 dates.

I have 2 date fields 'start date ','end date' , i need to calculate the 1 occurred date of 'start date ' and last occurred date of 'end date' .

Thank you all

Re: Expression for Calculating 1st occurrence date

ecolomer — Thu, 08 Jan 2015 06:07:36 GMT

simply have the difference between both dates

Re: Expression for Calculating 1st occurrence date

Thu, 08 Jan 2015 06:09:06 GMT

I didn't get you...

Re: Expression for Calculating 1st occurrence date

robert_mika — Thu, 08 Jan 2015 06:09:56 GMT

Does that mean that you have multiple start and end days?

Re: Expression for Calculating 1st occurrence date

anbu1984 — Thu, 08 Jan 2015 06:10:19 GMT

=Interval(EndDt-StartDt,'d')

=Interval(Date#('1/1/2015','M/D/YYYY')-Date#('1/1/2014','M/D/YYYY'),'d')

Re: Expression for Calculating 1st occurrence date

Thu, 08 Jan 2015 06:11:39 GMT

Exactly Robert..

posted here i didn't get any reply there..

Calculating Delay between 2 dates

Re: Expression for Calculating 1st occurrence date

its_anandrjs — Thu, 08 Jan 2015 06:15:06 GMT

Hi,

If you are asking for date difference try one of this

([end date] - [start date]) as Diff,

Interval ([end date] - [start date],'d') as Diff,

Regards

Anand

Re: Expression for Calculating 1st occurrence date

its_anandrjs — Thu, 08 Jan 2015 06:18:52 GMT

Hi,

It seems your date are text so then try to convert them in Date format then try Interval or date difference

Ex:-

Interval ([end date] - [start date],'d') as Diff,

//Without converting you can use this way.

Interval( Date(Date#([end date],'MM/DD/YYYY'),'MM/DD/YYYY') - Date(Date#([start date],'MM/DD/YYYY'),'MM/DD/YYYY'),'d') as Diff,

Regards

Anand

Re: Expression for Calculating 1st occurrence date

Thu, 08 Jan 2015 06:24:26 GMT

It is fine but i have multiple dates , i need to calculate only 1 date from multiple dates,i.e greater date than creation date.

Re: Expression for Calculating 1st occurrence date

Thu, 08 Jan 2015 06:30:25 GMT

Further clear understanding of requirement i created sample data.

1.1st Occurrence date of Forecast date and Last Occurrence date of Event Date and both should be Greater than Creation Date.

2. Decision should be Pass.

3. Also should calculate if Forecast Date of the Stage should be greater than previous pass Event Date

Example - While calculating Phase 2 Col 12 Forecast date is Greater than Col 9 Event date and it is passed at Phase 1 on 10/7/2014

S.no	ID	Name	Stage	Forecast Date	Event Date	Decision	Creation Date	Delay In No of Days
1	61	PV	Phase 1	9/12/2014			9/1/2014
2	61	PV	Phase 1	10/5/2014			9/1/2014
3	61	PV	Phase 1	10/9/2013			9/1/2014
4	61	PV	Phase 1	11/13/2014			9/1/2014
5	61	PV	Phase 1	10/3/2014			9/1/2014
6	61	PV	Phase 1		9/3/2014	Pass	9/1/2014
7	61	PV	Phase 1		9/3/2014	Cancelled	9/1/2014
8	61	PV	Phase 1		10/7/2014	Pass	9/1/2014
9	61	PV	Phase 1		10/7/2014	Pass	9/1/2014	25
10	61	PV	Phase 2	10/10/2014			9/1/2014
11	61	PV	Phase 2	10/5/2014			9/1/2014
12	61	PV	Phase 2	10/10/2014			9/1/2014
13	61	PV	Phase 2	10/8/2014			9/1/2014
14	61	PV	Phase 2	9/10/2014			9/1/2014
15	61	PV	Phase 2	10/9/2014			9/1/2014
16	61	PV	Phase 2		10/7/2014	Pass	9/1/2014
17	61	PV	Phase 2		11/11/2014	Pass	9/1/2014
18	61	PV	Phase 2		11/17/2014	Pass	9/1/2014	38

Re: Expression for Calculating 1st occurrence date

ecolomer — Thu, 08 Jan 2015 06:31:21 GMT

Try this:

=floor(End_Date)- floor(Start_Date) as [delay in no of days]