topic Re: Month in QlikView

Month

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

Hi Experts,

I have in date in Excel like

STARTDATE ENDDATE

27/03/2016 31/05/2016

I need output like

Re: Month

qlikview979 — Mon, 20 Jun 2016 06:40:50 GMT

Output like this

2016_mar

2016_apr

2016_may

Re: Month

PrashantSangle — Mon, 20 Jun 2016 06:43:30 GMT

Hi,

using those 2 field you want create one field????

> use master calendar

For that formating

> use date(dateField,'YYYY_MMM')

Regards

Re: Month

Anonymous — Mon, 20 Jun 2016 06:55:27 GMT

Why not check this?

The Date Function

Re: Month

yadav_anil782 — Mon, 20 Jun 2016 07:09:45 GMT

PLZ TRY THIS

LOAD

date,

date(date,'YYYY-MMM') AS Date

FROM

(qvd);

Re: Month

muthukumar77 — Mon, 20 Jun 2016 07:20:18 GMT

Hi,

Can you send me a sample QVW file?

Re: Month

qlikview979 — Mon, 20 Jun 2016 07:24:34 GMT

Hi Anil,

Please check my source& output . I don't have April Month in source ,but i want April month also.

Regards,

Mahesh.

Re: Month

qlikview979 — Mon, 20 Jun 2016 07:29:04 GMT

please find the attached file

Re: Month

jonathandienst — Mon, 20 Jun 2016 07:31:42 GMT

Load like this:

LOAD

Date(MonthStart(STARTDATE), 'yyyy-MMM') as STARTDATE,

Date(MonthStart(ENDDATE), 'yyyy-MMM') as ENDDATE,

...

I would use MonthStart or MonthEnd to ensure a unique value for each month. Otherwise you could land up with multiple dates formatted as (for example) "2016-Jun", so they look the same, but have different underlying values (1 Jun, 2 Jun etc).

Re: Month

qlikview979 — Mon, 20 Jun 2016 07:36:51 GMT

Hi Jonathan,

Please check my above attached Qvw.

Regards,

Mahesh.

Re: Month

Anil_Babu_Samineni — Mon, 20 Jun 2016 07:53:58 GMT

Follow this.

I just added Years if you want to add Months please change that and let me know

- Anil Chowdary

Re: Month

yadav_anil782 — Mon, 20 Jun 2016 07:58:17 GMT

Hi Mahesh ,

if your dates are missing then you have to find missing dates between min and max date with loop and min - max variable - this link will help to understand this proccess - Creating A Master Calendar

Temp:
Load
min(date) as minDate,
max(date) as maxDate
Resident table;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

Re: Month

qlikview979 — Mon, 20 Jun 2016 08:07:51 GMT

Hi Anil

I Don't want Missing Dates This is my input:-

I have in date in Excel like

STARTDATE ENDDATE

27/03/2016 31/05/2016

Here April month data is not there but i need output given below like this.

Expected output:-

Date:-

2016_Mar

2016_Apr

2016_May

Re: Month

varshavig12 — Mon, 20 Jun 2016 08:17:15 GMT

For End Date

=date(date#(ENDDATE,'DD/MM/YYYY'),'YYYY')&'_'&date(date#(ENDDATE,'DD/MM/YYYY'),'MMM')

Re: Month

varshavig12 — Mon, 20 Jun 2016 08:20:27 GMT

Output:

End_date:

=date(date#(ENDDATE,'DD/MM/YYYY'),'YYYY')&'_'&date(date#(ENDDATE,'DD/MM/YYYY'),'MMM')

Start_date:

=date(date#(STARTDATE,'DD/MM/YYYY'),'YYYY')&'_'&date(date#(STARTDATE,'DD/MM/YYYY'),'MMM')

Re: Month

qlikview979 — Mon, 20 Jun 2016 08:27:14 GMT

Hi Varsha,

I done like this

DATE(Date#(STARTDATE,'DD/MM/YYYY'),'YYYY_MMM') as STARTDATE,

DATE(Date#(ENDDATE,'DD/MM/YYYY'),'YYYY_MMM') AS ENDDATE,

I got like this

STARTDATE ENDDATE

2016_Mar 2016_May

Here I don't have April data I want April month also below like this.

But I need output :-

Date:-

2016_Mar,

2016_Apr,

2016_May

Regards,

Mahesh

Re: Month

Anil_Babu_Samineni — Mon, 20 Jun 2016 08:28:47 GMT

Mahesh,

Did you try my attachment.

Re: Month

varshavig12 — Mon, 20 Jun 2016 08:36:00 GMT

I'm sorry.

I missed out April.

I will try and let you know

Re: Month

Digvijay_Singh — Mon, 20 Jun 2016 08:49:45 GMT

Not sure if you just want this for representation of these two dates only -

Re: Month

Anonymous — Mon, 20 Jun 2016 08:50:53 GMT

Hi Mahesh,

See this code:

Data:

LOAD *,

Date(DateInc) as Date;

LOAD * INLINE [

DateInc, Value

01/05/2016, 10

01/06/2016, 20

01/08/2016, 50

];

LEFT JOIN

LOAD

Date,

If(IsNull(Previous(Date)),'31/12/2016',

Previous(Date)-1) as PrevDate

RESIDENT Data

ORDER BY Date DESC;

Calendariced:

Load *,

Month(Date) as Month,

Year(Date) as Year,

MonthName(Date) as MonthYear;

LOAD

Value,

Date + Iterno()-1 As Num,

Date(Date + IterNo() - 1) as Date

RESIDENT Data

While Date + IterNo() -1 <= PrevDate;

DROP TABLE Data;

EXIT Script;

Regards!