Several Dates in model

[guillaume_gorli]

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.

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 :

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

[sunny_talwar · Accepted solution]

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;

[guillaume_gorli]

Thanks you very much Sunny.

Really appreciate your help

Guillaume