Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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.
The Max function has an alternative rank option, use
Max(Week,2)
And it will give you the required second largest week.