Generate Months between two Months

[agni_gold]

Hi All ,

I am creating a calender , I want to create Quarter months as per date selection.

Suppose i select 01-Jan-2014,

Then Quarter will be Jan,Feb,Mar so i want to generate a Quarter Months field which will sort based on my date selection.

My Script is

Let varMinDate = Num(MakeDate(2014,1,1));

Let varMaxDate = Num(YearEnd(MakeDate(2014,1,1)));

Temp_Date:

LOAD date($(varMinDate)+IterNo()-1) AS Temp_Date AUTOGENERATE (1) WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

Calender:

LOAD Date(Temp_Date,'DDMMMYYYY')                                           AS Date,

     Date(Temp_Date,'DDMMMYYYY')                                           AS [Order Date],

  Year(Temp_Date)                                                       AS Year,

  Month(YearStart(Temp_Date))                                           AS [Year Start Month],

  Month(YearEnd(Temp_Date))                                             AS [Year End Month],

  Month(YearEnd(Temp_Date)-YearStart(Temp_Date))                        AS [Year Months],

  Month(Temp_Date)                                                      AS Month,

  Dual(Date((Temp_Date),'MMMM'),Num(Month(Temp_Date)))                  AS Months,

  Floor(Monthstart(Temp_Date))                                          AS Monthstart,

  Week(Temp_Date)                                                       AS [Week],

     Weekday(Temp_Date)                                                    AS [Days Name],

  Day(Temp_Date)                                                        AS Day,

  'Q ' & Num(Ceil(Month(Temp_Date)/3),'(ROM)0')                         AS Quarter,

  Month(QuarterStart(Temp_Date))                                        AS [Quarter Start Month],

  Month(QuarterEnd(Temp_Date))                                          AS [Quarter End Month],

  Month(QuarterEnd(Temp_Date)-QuarterStart(Temp_Date))                  AS [Quarter Months],

  Monthname(Temp_Date)                                                  AS [Month Name],

  Num(Monthname(Temp_Date))                                             AS [Rooling Month],

  (Num(Floor(Monthend(Temp_Date)))-Num(Floor(Monthstart(Temp_Date)))+1) AS [Number of Days in Month],

  If(Num(Weekday(Temp_Date))<5,1,0)                                     AS [Working Days]

RESIDENT Temp_Date;

DROP TABLE Temp_Date;

[swuehl]

Not sure what you are trying to achieve, you already have a Quarter field in your data model that should group your dates accordingly.

If you want to select a Date, but then use full quarter in your aggregation, you can use set analysis:

=Sum({<Quarter = p(), Date= >} Value)

or if you want quarters only for possible year:

=Sum({<Quarter = p(), Year=p(),  Date= >} Value)

You would need to clear all selections in other calendar fields the user may select in, just like for Date field, to avoid incompatible set definitions.

[agni_gold]

Thanks,

But my requirement is to create in calender script.

Is this possible ?

[Anonymous]

Hi ,

TRy this:

LOAD
[$(_field)]
,year([$(_field)]) as [$(_prefix)Year]
,month([$(_field)]) as [$(_prefix)Month]
,day([$(_field)]) as [$(_prefix)Day]
,weekday([$(_field)]) as [$(_prefix)Weekday],
'Q' & ceil(Num(month([$(_field)]))/3) as [$(_prefix)Qtr]
;
// Generate range of dates between min and max.
LOAD
date(DateMin + IterNo()) as [$(_field)] // Link Field
WHILE DateMin + IterNo() <= DateMax
;
// Find min and max of date field values.
LOAD
min(datefield)-1 as DateMin
,max(datefield) as DateMax
;
// Load date field values.
LOAD
FieldValue('$(_field)', RecNo()) as datefield
AutoGenerate FieldValueCount('$(_field)');

END SUB

Regards,

Bunny

[agni_gold]

its not creating months for quater

[swuehl]

Seems like I don't understand what you want to do.

I think you already have everything you need in your script.

Please explain the way the user wants to interact a bit more detailed.

[Anonymous]

hi agnivesh,

Maybe try as below:

Let varMinDate = Num(MakeDate(2014,1,1));
Let varMaxDate = Num(YearEnd(MakeDate(2014,1,1)));

QuarterMap:
MAPPING LOAD * INLINE [
Month, Quarter
1, Q1
2, Q1
3, Q1
4, Q2
5, Q2
6, Q2
7, Q3
8, Q3
9, Q3
10, Q4
11, Q4
12, Q4
];

Temp_Date:
LOAD date($(varMinDate)+IterNo()-1) AS Temp_Date AUTOGENERATE (1) WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);
Calender:
LOAD Date(Temp_Date,'DDMMMYYYY') AS Date,
Date(Temp_Date,'DDMMMYYYY')  AS [Order Date],
Year(Temp_Date) AS Year,
Month(YearStart(Temp_Date)) AS [Year Start Month],
Month(YearEnd(Temp_Date))   AS [Year End Month],
Month(YearEnd(Temp_Date)-YearStart(Temp_Date)) AS [Year Months],
Month(Temp_Date) AS Month,
Dual(Date((Temp_Date),'MMMM'),Num(Month(Temp_Date))) AS Months,
Floor(Monthstart(Temp_Date)) AS Monthstart,
Week(Temp_Date) AS [Week],
Weekday(Temp_Date) AS [Days Name],
Day(Temp_Date) AS Day,
  applyMap('QuarterMap', num(month(Temp_Date)),'unknown qtr') AS Quarter,
  //'Q ' & Num(Ceil(Month(Temp_Date)/3),'(ROM)0') AS Quarter,
  Month(QuarterStart(Temp_Date))  AS [Quarter Start Month],
Month(QuarterEnd(Temp_Date))     AS [Quarter End Month],
Month(QuarterEnd(Temp_Date)-QuarterStart(Temp_Date))  AS [Quarter Months],
Monthname(Temp_Date) AS [Month Name],
Num(Monthname(Temp_Date)) AS [Rooling Month],
(Num(Floor(Monthend(Temp_Date)))-Num(Floor(Monthstart(Temp_Date)))+1) AS [Number of Days in Month],
If(Num(Weekday(Temp_Date))<5,1,0) AS [Working Days]
RESIDENT Temp_Date;
DROP TABLE Temp_Date;

[Anonymous]

Agnivesh

I am also unsure as to exactly what you are after, but maybe creating a dual field called, say, QuarterDual could help

  if ( Ceil(Month(Temp_Date)/3) = 1 , dual(Quartername(Temp_Date) , 1  ) ,

  if ( Ceil(Month(Temp_Date)/3) = 2 , dual(Quartername(Temp_Date) , 2  ) ,

  if ( Ceil(Month(Temp_Date)/3) = 3 , dual(Quartername(Temp_Date) , 3  ) ,

  if ( Ceil(Month(Temp_Date)/3) = 4 , dual(Quartername(Temp_Date) , 4  ))))) as QuarterDual ,

Then you cam sort on it numerically and display it as text.

Instead of Quartername(Temp_Date) you could use text strings like 'Jan, Feb, Mar' or whatever you want.

See attached.

[agni_gold]

Friends ,

Actually i want to make this field in my load script

Please help me on this

[Anonymous]

It is for the load script, that's why it has the as QuarterDual . Just copy and paste it in, or see the qvw I attached.