Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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.
Thanks,
But my requirement is to create in calender script.
Is this possible ?
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
its not creating months for quater
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.
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;
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.
Friends ,
Actually i want to make this field in my load script
Please help me on this
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.