Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Thanks you very much Sunny.
Really appreciate your help
Guillaume