Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everybody,
I need to compare data between the 1st quarter 2017 versus the 1st quarter 2016.
How can i modify this monthly calendar ?
Thank you for you help.
------------------------------------------------------------------------------------------------------------------------------------
CAL_TMP:
LOAD distinct makedate(subfield(@CLE_Date,'-',1),subfield(@CLE_Date,'-',2),'01') as Monthstart Resident Table1;
OUTER JOIN LOAD distinct makedate(subfield(@CLE_Date,'-',1),subfield(@CLE_Date,'-',2),'01') as Monthstart Resident Table2;
OUTER JOIN LOAD distinct makedate(subfield(@CLE_Date,'-',1),subfield(@CLE_Date,'-',2),'01') as Monthstart Resident Table3;
CAL_TMP2:
LOAD
max(Monthstart) as MAX_DATE,
min(Monthstart) as MIN_DATE
Resident CAL_TMP;
DROP TABLE CAL_TMP;
let Var.Date.Min=num(Peek('MIN_DATE')-1);
let Var.Date.Max=num(Peek('MAX_DATE'));
let Var.Nb.Days=Var.Date.Max-Var.Date.Min;
Calendrier:
LOAD distinct
YEAR($(Var.Date.Min)+RecNo())&'-'&NUM(Month($(Var.Date.Min)+RecNo()),'00') as [@CLE_Date],
YEAR($(Var.Date.Min)+RecNo())&'-'&NUM(Month($(Var.Date.Min)+RecNo()),'00') as [AnnéeMois],
MonthName($(Var.Date.Min)+RecNo()) as [AnnéeMois Nom],
YEAR($(Var.Date.Min)+RecNo()) as [Année],
Month($(Var.Date.Min)+RecNo()) as [Mois Nom],
NUM(Month($(Var.Date.Min)+RecNo()),'00') as [Mois],
Date($(Var.Date.Min)+RecNo()) as Date
AUTOGENERATE($(Var.Nb.Days));
DROP TABLE CAL_TMP2;
let Var.Date.Min=null();
let Var.Date.Max=null();
let Var.Nb.Days=null();
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
please post some sample data and your expected result.
thanks
regards
Marco
Hello ,
It is difficult to make sample data from a big application.
But i included the function QuarterName in the calendar :
QuarterName($(Var.Date.Min)+RecNo()) as nom_trim,
And it works.
I can get the Sum of Visits of a selected quarter :
sum( {<mois_trim >}Visites)
Now i need to get the evolution rate beetween 2 quarters as following :
the 4th quarter 2016 vs the 4th quarter 2015
the 1st quarter 2017 vs the 1st quarter 2016
How can i write the set analysis to get the quarter of the year before the year selected ?
I tried this but it does not work :
sum( {<Année = {$(=num(Max (Année)-1 ,'00'))}, mois_trim} >} Visites)
Thank you for your help.
Regards,