topic Re: Date range in QlikView

Date range

upaliwije — Wed, 13 Sep 2017 05:25:27 GMT

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

Re: Date range

Anil_Babu_Samineni — Wed, 13 Sep 2017 05:32:44 GMT

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;

Re: Date range

upaliwije — Wed, 13 Sep 2017 05:56:49 GMT

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 ?

Re: Date range

Anil_Babu_Samineni — Wed, 13 Sep 2017 06:33:15 GMT

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..

Re: Date range

upaliwije — Wed, 13 Sep 2017 06:56:40 GMT

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

Re: Date range

Kushal_Chawda — Wed, 13 Sep 2017 07:00:48 GMT

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

Re: Date range

upaliwije — Wed, 13 Sep 2017 07:18:18 GMT

Yes

Re: Date range

Kushal_Chawda — Wed, 13 Sep 2017 07:43:11 GMT

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

Re: Date range

upaliwije — Wed, 13 Sep 2017 08:00:52 GMT

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

Re: Date range

Anonymous — Wed, 13 Sep 2017 08:04:46 GMT

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;

Re: Date range

Anil_Babu_Samineni — Wed, 13 Sep 2017 08:28:17 GMT

Until how may conditions you need to develop? Currently, It showing 3

Re: Date range

antoniotiman — Wed, 13 Sep 2017 08:30:38 GMT

Hi Upali,

See Attachment.

I changed Section 'Calendar' of Script.

Remove Replace from RISKCALENDAR Table in Script.

Regards,

Antonio

Re: Date range

upaliwije — Wed, 13 Sep 2017 09:02:48 GMT

only 3

Re: Date range

upaliwije — Wed, 13 Sep 2017 09:38:44 GMT

Your Solution seems alright and I have modified your script to be more user friendly as follows. The underlined part I can not write properly. Pls check and Advise. I ma selecting max(Risk_rate) because in certain months you do get 31st day Eg Feb

LOAD

Day(RISK_DATE) AS RISK_DAY,

Date(RISK_DATE) AS RISK_DATE,

Week(RISK_DATE) AS RISK_WEEK,

Year(RISK_DATE) AS RISK_YEAR,

Month(RISK_DATE) As RISK_MONTH,

Month(RISK_DATE) &'-'& YEAR(RISK_DATE) As RISK_Month_Year,

Month(RISK_DATE) &'-'& week(RISK_DATE) As RISK_Month_Week,

If(Day(RISK_DATE) >= 1 and Day(RISK_DATE) <= 14,'1-14 '&Month(RISK_DATE),

If(Day(RISK_DATE) >= 15 and Day(RISK_DATE) <= 31,'15-'&MAX(RISK_DATE)''&Month(RISK_DATE))) as Period,

If(Day(RISK_DATE) >= 15 and Day(RISK_DATE) <= 31,'15 '&Month(RISK_DATE)&'-14 '&Month(AddMonths(RISK_DATE,1)),

'15 '&Month(AddMonths(RISK_DATE,-1))&'-14 '&Month(RISK_DATE))as PERIOD1

;

Load Date(TRN_MinDate + IterNo() -1 ) AS RISK_DATE While (TRN_MinDate + IterNo() - 1) <= Num(TRN_MaxDate);

Load

Min(RISK_DATE) AS TRN_MinDate,

Max(RISK_DATE) AS TRN_MaxDate

RESIDENT growth;

Re: Date range

antoniotiman — Wed, 13 Sep 2017 09:51:37 GMT

It seems & is missing

,'15-'&MAX(RISK_DATE)&'-'&Month(RISK_DATE))) as Period,

Re: Date range

upaliwije — Wed, 13 Sep 2017 09:57:51 GMT

Thanks Antonio

Then I get below error, I can't figure out how to correct it. pls help

Re: Date range

antoniotiman — Wed, 13 Sep 2017 10:12:13 GMT

You can't use Max(RISK_DATE) because You'd need Group By

Re: Date range

upaliwije — Wed, 13 Sep 2017 10:45:14 GMT

Thanks

If you pls can write me script for it

Re: Date range

antoniotiman — Wed, 13 Sep 2017 11:27:04 GMT

Day(MonthEnd(RISK_DATE)) instead of Max(RISK_DATE)