Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
guillaume_gorli
Creator II
Creator II

Several Dates in model

Hi All,

I know this issue has been adressed a lot o f time but i cannot make it.

I have a very simple database showing two dates : one requested date and one validated date with an ID for each line of the database.

So two dates in one model.

Capture.JPG

What i need to produce is a table showing per month the quantity of action requested and the quantity of action validated as shown below :

Capture.JPG

Cherry on the cake :  i need to determine the average leadtime between the two dates for each period.

I have tried to build canonical date as explained in different topics but i may miss something somewhere, i cannot make it work and duplicate to my simple model.

I attached test database and .qvw for those who want to help

Thanks in advance

Guillaume

1 Solution

Accepted Solutions
sunny_talwar

Try this script

/// TEST ///

Table:

LOAD [Action reference],

    [Date emission],

    [Date validation]

FROM

[TEST (1).xlsx]

(ooxml, embedded labels, table is Feuil1);


LinkTable:

LOAD Distinct [Action reference],

[Date emission] as Date,

'Emission' as Flag

Resident Table;


Concatenate (LinkTable)

LOAD Distinct [Action reference],

[Date validation] as Date,

'Validation' as Flag

Resident Table;


LET vMinDate = Num(Makedate(2012,1,1));

LET vMaxDate = Num(Makedate(2018,12,31));


ChampDate:

LOAD date($(vMinDate)+IterNo()-1) AS MaDate

AUTOGENERATE (1)

WHILE $(vMinDate)+IterNo()-1<= $(vMaxDate);


Calendrier:

LOAD

MaDate as Date,

Year(MaDate) as Année,

'Q ' & Num(Ceil(Month(MaDate)/3),'(ROM)0') as Trimestre,

Month(MaDate) as Mois,

Monthstart (MaDate) as DateDebutMois,

MonthEnd(MaDate)as DateFinMois,

Week(MaDate) as Semaine,

Weekday(MaDate) as JourSemaine,

Day(MaDate) as Jour,

MonthName(MaDate) as Année_Mois

RESIDENT ChampDate;

Capture.PNG

View solution in original post

2 Replies
sunny_talwar

Try this script

/// TEST ///

Table:

LOAD [Action reference],

    [Date emission],

    [Date validation]

FROM

[TEST (1).xlsx]

(ooxml, embedded labels, table is Feuil1);


LinkTable:

LOAD Distinct [Action reference],

[Date emission] as Date,

'Emission' as Flag

Resident Table;


Concatenate (LinkTable)

LOAD Distinct [Action reference],

[Date validation] as Date,

'Validation' as Flag

Resident Table;


LET vMinDate = Num(Makedate(2012,1,1));

LET vMaxDate = Num(Makedate(2018,12,31));


ChampDate:

LOAD date($(vMinDate)+IterNo()-1) AS MaDate

AUTOGENERATE (1)

WHILE $(vMinDate)+IterNo()-1<= $(vMaxDate);


Calendrier:

LOAD

MaDate as Date,

Year(MaDate) as Année,

'Q ' & Num(Ceil(Month(MaDate)/3),'(ROM)0') as Trimestre,

Month(MaDate) as Mois,

Monthstart (MaDate) as DateDebutMois,

MonthEnd(MaDate)as DateFinMois,

Week(MaDate) as Semaine,

Weekday(MaDate) as JourSemaine,

Day(MaDate) as Jour,

MonthName(MaDate) as Année_Mois

RESIDENT ChampDate;

Capture.PNG

guillaume_gorli
Creator II
Creator II
Author

Thanks you very much Sunny.

Really appreciate your help

Guillaume