topic Re: Generate Months between two Months in QlikView

Generate Months between two Months

agni_gold — Sun, 18 Oct 2015 13:04:54 GMT

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;

Re: Generate Months between two Months

swuehl — Sun, 18 Oct 2015 13:46:06 GMT

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.

Re: Generate Months between two Months

agni_gold — Sun, 18 Oct 2015 13:49:46 GMT

Thanks,

But my requirement is to create in calender script.

Is this possible ?

Re: Generate Months between two Months

Anonymous — Sun, 18 Oct 2015 13:54:46 GMT

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

Re: Generate Months between two Months

agni_gold — Sun, 18 Oct 2015 14:02:43 GMT

its not creating months for quater

Re: Generate Months between two Months

swuehl — Sun, 18 Oct 2015 14:03:46 GMT

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.

Re: Generate Months between two Months

Anonymous — Sun, 18 Oct 2015 14:16:34 GMT

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;

Re: Generate Months between two Months

Anonymous — Sun, 18 Oct 2015 16:32:04 GMT

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.

Re: Generate Months between two Months

agni_gold — Sun, 18 Oct 2015 16:56:19 GMT

Friends ,

Actually i want to make this field in my load script

Please help me on this

Re: Generate Months between two Months

Anonymous — Sun, 18 Oct 2015 17:22:51 GMT

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.

Re: Generate Months between two Months

Anonymous — Mon, 19 Oct 2015 05:28:58 GMT

Hi Agnivesh,

all the given solutions are for the load script ,if these is not you are looking for please elaborate on your query?

Regards

Neetha