Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

Predefine Date range

Dear Community,

May I know how to define date range in the script?

Requirement:

Used Dimension =

Week1 - Week6,

Week2 - Week7,

Week3 - Week8,

...

Expression =

Sum(Sales)

The first bar in the chart will show sum of sales from week1-week6,

2nd bar will show sum of sales from week2-week7,

3rd bar will show sum of sales from week3-week8...

I tried with this script :

'W' & Ceil(week(TempDate)/6) As ROIWeek

but it is not working as desired, it gives me 6 weeks which is week1-week6, week7-week12, week13-week18...

Not suit the requirement.

The master calendar code is as shown below:

...
MasterCalendar:
LOAD TempDate AS [Date],
     Date(TempDate,'DD/MM/YY') as ShortDate,
     //week(TempDate) AS Week,
     year(TempDate) AS Year,
     month(TempDate) AS Month,
     if(Month(TempDate)='Jan',1,
       if(Month(TempDate)='Feb',2,
       if(Month(TempDate)='Mar',3,
       if(Month(TempDate)='Apr',4,
       if(Month(TempDate)='May',5,
       if(Month(TempDate)='Jun',6,
       if(Month(TempDate)='Jul',7,
       if(Month(TempDate)='Aug',8,
       if(Month(TempDate)='Sep',9,
       if(Month(TempDate)='Oct',10,
       if(Month(TempDate)='Nov',11,
       12
       ))))))))))) as Month_Number,
     day(TempDate) AS Day,
     weekday(TempDate) AS WeekDay,
     'Q' & Ceil(Month(TempDate)/3) AS Quarter,
     'Q' & Ceil(Month(TempDate)/3)&'-'&right(year(TempDate),2) AS QuarterYear,
     date(monthstart(TempDate), 'MMM YY') AS MonthYear,
     //week(TempDate)&'-'&right(Year(TempDate),2) AS WeekYear,
     Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CueYTDFlag,
       Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag
RESIDENT TempCalendar
ORDER BY TempDate Asc;
DROP Table TempCalendar,Temp;

Thanks and best regards,

Chanel

1 Solution

Accepted Solutions

Re: Re: Predefine Date range

Hi

Try like this

PFA

Hope it helps

10 Replies
MVP & Luminary
MVP & Luminary

Re: Predefine Date range

For that you'll need another table that links the week with every range it is part of. You'll create the week field in the master calendar and then add another table with the range and week number. That would something like this:

Weekranges:

load * inline [

Range, Week

Week1 - Week6, 1

Week1 - Week6, 2

...etc

Week1 - Week6, 6

Week2 - Week7, 2

Week2 - Week7, 3

...etc

Week2 - Week7, 7

..etc

];


Note, the inline load is just an example. You can create the table any way you want, for example from loading an excel file.


talk is cheap, supply exceeds demand
Not applicable

Re: Predefine Date range

Thanks for prompt response!

But it's kinda impossible for us to update the .qvw script every week.

Initially i think of :

'W' & Ceil(week(TempDate)/6) As ROIWeek

But it's not working according to requirement, it gives week1-6, 7-12...

Any idea like while loop or for loop that will automatically create a date range?

Best regards,

Chanel

MVP & Luminary
MVP & Luminary

Re: Predefine Date range

You can probably generate it with something like

Ranges:

Load distinct Week, Week +1 - iterno() as RangeNumber

resident MasterCalendar

while Week + IterNo()-1 < Week+6 ;


talk is cheap, supply exceeds demand

Re: Re: Predefine Date range

Hi

Try like this

PFA

Hope it helps

Not applicable

Re: Predefine Date range

Awesome!

Many thanks to Mayil Vahanan Ramasamy and G Wassenaar!!!

One additional hint needed is how can I start the WeekDim from 6 instead of 1?

Load Distinct

    Week(Date) as WeekDim,

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

Resident MasterCalendar

while Week + IterNo()-1 <= Week+5 ;

Best regards,

Chanel

Re: Predefine Date range

Do you need like this? Please explain little more?

Load Distinct

    Week(Date) + 5 as WeekDim,

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

Resident MasterCalendar

while Week + IterNo()-1 <= Week+5 ;


or


Load Distinct

    Week(Date) as WeekDim,

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

Resident MasterCalendar

Where Week(Date) > 5

while Week + IterNo() <= Week + 5 ;

Not applicable

Re: Predefine Date range

Thanks for proposed solution! It helped to start the week at week6 but at the end, it will add another 5 weeks to actual week as well...

Load Distinct

    Week(Date) + 5 as WeekDim,

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

Resident MasterCalendar

while Week + IterNo()-1 <= Week+5 ;


for the 2nd solution, i think it is not allowed to use where and while at the same time, or there is additional syntax needed?

Load Distinct

    Week(Date) as WeekDim,

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

Resident MasterCalendar

Where Week(Date) > 5

while Week + IterNo() <= Week + 5 ;


Thanks and best regards,

Chanel

Re: Predefine Date range

Hi

Try like this

Load * where WeekDim > 5;

Load Distinct Week(Date) as WeekDim, Week(Date) + IterNo()-1 as Week

Resident Tablename

while Week + IterNo()-1 <= Week+5 ;

Not applicable

Re: Predefine Date range

Hi,

I tried to modify to :

Load Distinct Week(Date) +5 as WeekDim, Week(Date) + IterNo()-1 as Week

Resident MasterCalendar

while Week + IterNo()-1 <= Week+5 ;

Load * where WeekDim < Week-5;

But it is still not working, i can see the next 5 weeks which is not actual week.

The max week number is week 38, but i can see until week 44...

SOS...

Best regards,

Chanel