Date, Periods, Weeks & Quarters in QlikView

Fri, 10 Jan 2014 12:51:02 GMT

I am trying to figure out a problem which I cannot seem to solve.

MY PROBLEM

I have an Excel file with Years, Weeks and Quarters and they have the following structure

PERIOD	QUARTER	WEEK	YEAR
201401	201401	201401	2014
201313	201304	201352	2013
201313	201304	201351	2013
201313	201304	201350	2013
201313	201304	201349	2013
201312	201304	201348	2013
201312	201304	201347	2013
201312	201304	201346	2013
201312	201304	201345	2013
201311	201304	201344	2013
201311	201304	201343	2013
201311	201304	201342	2013
201311	201304	201341	2013
201310	201303	201340	2013
201310	201303	201339	2013
201310	201303	201338	2013
201310	201303	201337	2013
201309	201303	201336	2013
201309	201303	201335	2013
201309	201303	201334	2013
201309	201303	201333	2013
201308	201303	201332	2013
201308	201303	201331	2013
201308	201303	201330	2013
201308	201303	201329	2013
201307	201302	201328	2013
201307	201302	201327	2013
201307	201302	201326	2013
201307	201302	201325	2013
201306	201302	201324	2013
201306	201302	201323	2013
201306	201302	201322	2013
201306	201302	201321	2013
201305	201302	201320	2013
201305	201302	201319	2013
201305	201302	201318	2013
201305	201302	201317	2013
201304	201301	201316	2013
201304	201301	201315	2013
201304	201301	201314	2013
201304	201301	201313	2013
201303	201301	201312	2013
201303	201301	201311	2013
201303	201301	201310	2013
201303	201301	201309	2013
201302	201301	201308	2013
201302	201301	201307	2013
201302	201301	201306	2013
201302	201301	201305	2013
201301	201301	201304	2013
201301	201301	201303	2013
201301	201301	201302	2013
201301	201301	201301	2013
201213	201204	201252	2012
201213	201204	201251	2012
201213	201204	201250	2012
201213	201204	201249	2012
201212	201204	201248	2012
201212	201204	201247	2012
201212	201204	201246	2012
201212	201204	201245	2012
201211	201204	201244	2012
201211	201204	201243	2012
201211	201204	201242	2012
201211	201204	201241	2012
201210	201203	201240	2012
201210	201203	201239	2012
201210	201203	201238	2012
201210	201203	201237	2012
201209	201203	201236	2012
201209	201203	201235	2012
201209	201203	201234	2012
201209	201203	201233	2012
201208	201203	201232	2012
201208	201203	201231	2012
201208	201203	201230	2012
201208	201203	201229	2012
201207	201202	201228	2012
201207	201202	201227	2012
201207	201202	201226	2012
201207	201202	201225	2012
201206	201202	201224	2012
201206	201202	201223	2012
201206	201202	201222	2012
201206	201202	201221	2012
201205	201202	201220	2012
201205	201202	201219	2012
201205	201202	201218	2012
201205	201202	201217	2012
201204	201201	201216	2012
201204	201201	201215	2012
201204	201201	201214	2012
201204	201201	201213	2012
201203	201201	201212	2012
201203	201201	201211	2012
201203	201201	201210	2012
201203	201201	201209	2012
201202	201201	201208	2012
201202	201201	201207	2012
201202	201201	201206	2012
201202	201201	201205	2012
201201	201201	201204	2012
201201	201201	201203	2012
201201	201201	201202	2012
201201	201201	201201	2012

In my QlikView application, I have a list box which displays all these values in the table. Let's have a focus on the WEEK column in the above structure and the format it is in. (YYYYWW). The requirement for my application is to always have the front dashboard to display one less the maximum week number in the data. So if the maximum WEEK is 201352 then one less than this week is 201351. I have set an OnOpen trigger for the document to Toggle Select the WEEK filter to the following:

MAX(WEEK) - 1

This will acquire the maximum week number and get the one before it.

If the current week is 201401 (the first week of 2014) then when I apply the above function it gives me 201400, which is not the value I am after as I am after 201352.

Is there a way that I can get around this so that when I have a week I am deducting one week? Do I need to reconsider my function I am using?

If anyone can help then that would be great.

Re: Date, Periods, Weeks & Quarters in QlikView

Fri, 10 Jan 2014 14:56:19 GMT

Hi Rajesh, In such cases, you should create the another Field having counter value for each WEEK value. So use your new field Like below:

Calendar:

LOAD Distinct

PERIOD,

QUARTER,

WEEK,

YEAR

FROM

[http://community.qlik.com/thread/103912]

(html, codepage is 1252, embedded labels, table is @1);

ID:

LOAD WEEK , RowNo() AS WEEKID

Resident Calendar Order by WEEK;

Use WEEKID in the Expression: Max(WEEKID)-1

Please find the attached file for reference.

Re: Date, Periods, Weeks & Quarters in QlikView

Michiel_QV_Fan — Fri, 10 Jan 2014 15:04:44 GMT

Alternatively you can use autonumber in your script. 'order by week asc'.

This can be done in the Calender load statement which saves 1 resident load.

Re: Date, Periods, Weeks & Quarters in QlikView

stigchel — Fri, 10 Jan 2014 15:44:39 GMT

The Max function has an alternative rank option, use

Max(Week,2)

And it will give you the required second largest week.

topic Re: Date, Periods, Weeks & Quarters in QlikView in QlikView

Date, Periods, Weeks & Quarters in QlikView

Re: Date, Periods, Weeks & Quarters in QlikView

Re: Date, Periods, Weeks & Quarters in QlikView

Re: Date, Periods, Weeks & Quarters in QlikView