Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
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
MayilVahanan

Hi

Try like this

PFA

Hope it helps

Thanks & Regards,
Mayil Vahanan R

View solution in original post

10 Replies
Gysbert_Wassenaar

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
Author

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

Gysbert_Wassenaar

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
MayilVahanan

Hi

Try like this

PFA

Hope it helps

Thanks & Regards,
Mayil Vahanan R
Not applicable
Author

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

CELAMBARASAN
Partner - Champion
Partner - Champion

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
Author

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

MayilVahanan

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 ;

Thanks & Regards,
Mayil Vahanan R
Not applicable
Author

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