topic Re: DateMonth in QlikView

DateMonth

smilingjohn — Wed, 25 Nov 2020 16:16:04 GMT

Hi All

I have the master calendar which works perfectly

I just want to know how do we get the date like this

14-Jan

15-jan

30-dec now iam geting only month but ther is no date prefix to it

thanks

Re: DateMonth

kaushiknsolanki — Fri, 01 Apr 2016 07:11:12 GMT

Hi,

You can use the date function like below.

Date(DateField ,'DD-MMM') as DateMonth

Regards,

Kaushik Solanki

Re: DateMonth

Chanty4u — Fri, 01 Apr 2016 07:11:51 GMT

create datemonth field as

Month(Datefield,'DD-MMM') as DateMonth

Re: DateMonth

Kushal_Chawda — Fri, 01 Apr 2016 07:19:56 GMT

Do you have Date field in your data?

Re: DateMonth

smilingjohn — Fri, 01 Apr 2016 07:27:44 GMT

It works but the DateMonth field is showing all the date right from 1 to 30

I want it to display one weeks date for example like

14-Nov

21-Nov

28-Nov

.and so on

That is the begining of the week datmonth

Re: DateMonth

Kushal_Chawda — Fri, 01 Apr 2016 07:31:44 GMT

try this

date(Weekstart(DateField),'DD-MMM') as DateMonth

Re: DateMonth

kaushiknsolanki — Fri, 01 Apr 2016 07:56:28 GMT

Try this,

Weekname(Datefield) As week

Regards,

Kaushik Solanki

Re: DateMonth

smilingjohn — Fri, 01 Apr 2016 08:57:50 GMT

How to get the Only date month field for mondays ? (Starting from the first day of the week)

For example

like this snap shot

THanks

Re: DateMonth

kaushiknsolanki — Fri, 01 Apr 2016 09:03:19 GMT

If you just need for Monday then you can try this.

Load if(WeekDay(Date) = 'Mon',Date(Field,'DD-MMM')) as New Date

From xyz.

Regards,

Kaushik Solanki

Re: DateMonth

smilingjohn — Fri, 01 Apr 2016 09:12:15 GMT

Hi Kasuhi

I have the master Calendar already please look into the below script , out of my date field i want to get this DateMonth

MaxMin:

LOAD Max(Date) as MaxDate,

Min(Date) as MinDate

Resident FluData;

SET vFiscalYearStartMonth = 4;

let vStartDate=Peek('MinDate');

let vEndDate=Peek('MaxDate');

FiscalCalendar:

LOAD

Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD

Year(Date) AS Year, // Standard Calendar Year

Month(Date) AS Month, // Standard Calendar Month

week(Date) As Week,

Date(Date ,'DD-MMM') as DateMonth,

Date(MonthEnd(Date), 'MMM') AS MonthName, // Standard Calendar Month Name

//Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter, // Standard Calendar Quarter

'Q' & Ceil(Month(Date)/3) & '-' & Year(Date) as Quarter,

Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth, // Fiscal Month

Right(YearName(Date,0,$(vFiscalYearStartMonth)),4)as FiscalYear,

date(MonthStart(Date),'MMM-YY') AS MonthYear,

YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYearr; // Fiscal Year

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

Re: DateMonth

kaushiknsolanki — Fri, 01 Apr 2016 09:20:52 GMT

FiscalCalendar:

LOAD

Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD

Year(Date) AS Year, // Standard Calendar Year

Month(Date) AS Month, // Standard Calendar Month

week(Date) As Week,

//Date(Date ,'DD-MMM') as DateMonth,

if(WeekDay(Date) = 'Mon',Date(Date,'DD-MMM')) as DateMonth,

Date(MonthEnd(Date), 'MMM') AS MonthName, // Standard Calendar Month Name

//Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter, // Standard Calendar Quarter

'Q' & Ceil(Month(Date)/3) & '-' & Year(Date) as Quarter,

Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth, // Fiscal Month

Right(YearName(Date,0,$(vFiscalYearStartMonth)),4)as FiscalYear,

date(MonthStart(Date),'MMM-YY') AS MonthYear,

YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYearr; // Fiscal Year

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

Re: DateMonth

smilingjohn — Fri, 01 Apr 2016 09:47:18 GMT

Thanks Kaushik

But this is giving the DateMonth only for mnday but it should also give me the DateMonth for begning of the first date of the month and Ending of the monthDate.

Is this possible ?

Now what u have give definitely this is needed but it is giving me for monday and it did not show me the datemonth for the begning of the day.

For example date 1march started from wednesday , and 6th march is on monday ,current date month is showing from monday

I want it to show from the 1st march that is starting of the date day .

Re: DateMonth

kaushiknsolanki — Fri, 01 Apr 2016 09:49:30 GMT

So are you saying that you want the new field which is date and month for 1st and last Month of the day along with all Mondays?

Regards,

Kaushik Solanki

Re: DateMonth

Kushal_Chawda — Fri, 01 Apr 2016 10:00:25 GMT

try this

if(WeekDay(Date) = 'Mon' or monthstart(Date)=Date or monthend(Date)=Date,Date(Field,'DD-MMM')) as New Date

Re: DateMonth

smilingjohn — Fri, 01 Apr 2016 10:07:14 GMT

Hi Kaushik

Exactly That is what i want

Re: DateMonth

kaushiknsolanki — Fri, 01 Apr 2016 10:12:22 GMT

FiscalCalendar:

LOAD

Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD

Year(Date) AS Year, // Standard Calendar Year

Month(Date) AS Month, // Standard Calendar Month

week(Date) As Week,

//Date(Date ,'DD-MMM') as DateMonth,

if(WeekDay(Date) = 'Mon' or Floor(Date) = Floor(Monthstart(Date)) or Floor(Date) = Floor(Monthend(Date)),Date(Date,'DD-MMM')) as DateMonth,

Date(MonthEnd(Date), 'MMM') AS MonthName, // Standard Calendar Month Name

//Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter, // Standard Calendar Quarter

'Q' & Ceil(Month(Date)/3) & '-' & Year(Date) as Quarter,

Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth, // Fiscal Month

Right(YearName(Date,0,$(vFiscalYearStartMonth)),4)as FiscalYear,

date(MonthStart(Date),'MMM-YY') AS MonthYear,

YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYearr; // Fiscal Year

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;