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: 
upaliwije
Creator II
Creator II

Date range

I have some sales data stored in a table in date order

Date                Sales

01-01-2017     5000

02-01-2017    10000

......

31-08-2017    12000

I want to extract data for the following Periods

1.   1st to 14th of the month  Eg 01-01-17 to 14-01-17

2.   15th to 31st  of the month Eg: 15-01-17 to 31-01-17

3.    15th to 14th (two months) Eg : 15-01-17 to 14-02-17


How can I do this in script or master calendar pls help

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

Hi Upali,

See Attachment.

I changed Section 'Calendar' of Script.

Remove Replace from RISKCALENDAR Table in Script.

Regards,

Antonio

View solution in original post

18 Replies
Anil_Babu_Samineni

Try some thing like below?

Fact:

Load Date, Sales from Fact;

Concatenate (Fact)

Load If(Date >= '01-01-17' and <='14-01-17', Date) as Date,

          Sales,

          'Period 1' as Flag

Resident Fact;

Concatenate (Fact)

Load If(Date >= '15-01-17' and <='31-01-17', Date) as Date,

          Sales,

          'Period 2' as Flag

Resident Fact;

Concatenate (Fact)

Load If(Date >= '15-01-17' and <='14-02-17', Date) as Date,

          Sales,

          'Period 3' as Flag

Resident Fact;


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)
upaliwije
Creator II
Creator II
Author

Hi Anil,

I can do what you suggest for small date range .But In my data table I have millions of records ranging 1 -15 years. So it is practically difficult to do it.Is there a way to do it through master calendar ?

Anil_Babu_Samineni

Even, If you need master calendar off course, Bucket needed separately. And i believe you don't require master calendar over here. Can you provide more information related the same..

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)
upaliwije
Creator II
Creator II
Author

Anil

I have attached my sample data .I want Period Brackets as shown above to be against RISK_DATE. Kindly help me with best possible solution

Kushal_Chawda

Your buckets are fixed? I mean, 1 to 15, 15 to 31,  15 to 14 for two months?

upaliwije
Creator II
Creator II
Author

Yes

Kushal_Chawda

Is it ok if the same can be performed on front end, instead creating Flag in script?

upaliwije
Creator II
Creator II
Author

Better If could be done as script level, Anyway pls make available your suggested method

Anonymous
Not applicable

hi

I have Used this Logic with Demo of Master calender and its working fine

Load If(Date >= '01/01/2017' and Date <='14/01/2017', Date) as Date,

    'Period 1' as Flag

Resident MasterCalendar;

Load If(Date >= '15/01/2017' and Date<='31/01/2017', Date) as Date,    
  'Period 2' as Flag

Resident MasterCalendar;

Load If(Date >= '15/01/2017' and Date <='14/02/2017', Date) as Date,    
'Period 3' as Flag

Resident MasterCalendar;