Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2014-01-10 07:51 AM

Date, Periods, Weeks & Quarters in QlikView

Hi

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

PERIODQUARTERWEEKYEAR
2014012014012014012014
2013132013042013522013
2013132013042013512013
2013132013042013502013
2013132013042013492013
2013122013042013482013
2013122013042013472013
2013122013042013462013
2013122013042013452013
2013112013042013442013
2013112013042013432013
2013112013042013422013
2013112013042013412013
2013102013032013402013
2013102013032013392013
2013102013032013382013
2013102013032013372013
2013092013032013362013
2013092013032013352013
2013092013032013342013
2013092013032013332013
2013082013032013322013
2013082013032013312013
2013082013032013302013
2013082013032013292013
2013072013022013282013
2013072013022013272013
2013072013022013262013
2013072013022013252013
2013062013022013242013
2013062013022013232013
2013062013022013222013
2013062013022013212013
2013052013022013202013
2013052013022013192013
2013052013022013182013
2013052013022013172013
2013042013012013162013
2013042013012013152013
2013042013012013142013
2013042013012013132013
2013032013012013122013
2013032013012013112013
2013032013012013102013
2013032013012013092013
2013022013012013082013
2013022013012013072013
2013022013012013062013
2013022013012013052013
2013012013012013042013
2013012013012013032013
2013012013012013022013
2013012013012013012013
2012132012042012522012
2012132012042012512012
2012132012042012502012
2012132012042012492012
2012122012042012482012
2012122012042012472012
2012122012042012462012
2012122012042012452012
2012112012042012442012
2012112012042012432012
2012112012042012422012
2012112012042012412012
2012102012032012402012
2012102012032012392012
2012102012032012382012
2012102012032012372012
2012092012032012362012
2012092012032012352012
2012092012032012342012
2012092012032012332012
2012082012032012322012
2012082012032012312012
2012082012032012302012
2012082012032012292012
2012072012022012282012
2012072012022012272012
2012072012022012262012
2012072012022012252012
2012062012022012242012
2012062012022012232012
2012062012022012222012
2012062012022012212012
2012052012022012202012
2012052012022012192012
2012052012022012182012
2012052012022012172012
2012042012012012162012
2012042012012012152012
2012042012012012142012
2012042012012012132012
2012032012012012122012
2012032012012012112012
2012032012012012102012
2012032012012012092012
2012022012012012082012
2012022012012012072012
2012022012012012062012
2012022012012012052012
2012012012012012042012
2012012012012012032012
2012012012012012022012
2012012012012012012012

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.

1,469 Views
0 Likes
3 Replies
Not applicable
‎2014-01-10 09:56 AM
Author

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.

Comm103912.qvw
337 Views
Michiel_QV_Fan
Specialist
Specialist
‎2014-01-10 10:04 AM

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.

337 Views
0 Likes
stigchel
Partner - Master
Partner - Master
‎2014-01-10 10:44 AM

The Max function has an alternative rank option, use

Max(Week,2)

And it will give you the required second largest week.

337 Views
0 Likes