How to insert "quarters" in a monthly calendar ?

[marieclaire]

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();

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

[MarcoWedel]

please post some sample data and your expected result.

thanks

regards

Marco

[marieclaire]

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,