Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikview979
Specialist
Specialist

Month

Hi Experts,

I have in  date in Excel like

STARTDATE  ENDDATE

27/03/2016      31/05/2016

I need output like

39 Replies
Anil_Babu_Samineni

Follow this.

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

- Anil Chowdary

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
yadav_anil782
Creator II
Creator II

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

  1. Temp: 
  2. Load 
  3.                min(date) as minDate, 
  4.                max(date) as maxDate 
  5. Resident table; 
  6.  
  7. Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 
  8. Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 
  9. DROP Table Temp; 
  10.  
  11. TempCalendar: 
  12. LOAD 
  13.                $(varMinDate) + Iterno()-1 As Num, 
  14.                Date($(varMinDate) + IterNo() - 1) as TempDate 
  15.                AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 
qlikview979
Specialist
Specialist
Author

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

varshavig12
Specialist
Specialist

For End Date

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

varshavig12
Specialist
Specialist

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')

qlikview979
Specialist
Specialist
Author

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

Anil_Babu_Samineni

Mahesh,

Did you try my attachment.

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
varshavig12
Specialist
Specialist

I'm sorry.

I missed out April.

I will try and let you know

Digvijay_Singh

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

Capture.JPG

Anonymous
Not applicable

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!