Select last week from string??

[#1]

Hey there, guys im having a hard time trying to figure out out to select the last week from a field named "Week" with the folllowing formating: "W20-11",  im after a table to show me the last week only, and then im going to need to select the previous past 10 weeks before the last week to do an average, my problem is finding out how to do this when there is no dates loaded, the only dates present are made from the week field as

"makeweekdate('20'& right(Week,2),mid(Week,2,index(Week,'-')-2)) as Date.

Thanks

[#2]

So, you have a field loaded via script as follows:

Name: Week

Format: Wwn-YY where wn is week number in year YY

And, you have a field, apparently for display, as follows:

Name: Date

Format: DateTime internal, first day of a given week number

So, when you say there are no dates loaded, do you mean that the user has not selected any dates yet?

Or, do you need to make the selection of the last week a permanent condition of a chart, then show the rolling 10 average ending in the current (maximum) week in the data?

[#3]

The Week field is the week used for all selections, and the Date field is used for other expressions etc but never used for selections, what i meant is i dont have a field with actual dates, the only one i have that gets loaded into qlikview is the Week field, and what im after is a way to show the sales of the last week only, having in account that every week it passes, a new Week is loaded into the system, and then in the same table or a different one, i will have an average of the last 10 weeks that i will compare with the last week, to see if the last week is within average.

Thanks

[#4]

Hi ,

Can you please share some test data ... I will try to work it on.

Regards,

Chakravarthy.

[#5]

Hi thank you so much for the help, ive been trying for a few hours but i still have alot to learn.

So basically i just need to doa table that shows me the sales for the last week only, then im after an average for the last 10 weeks, but i cant seem to find a way to make a expression that selects specifics from the "Week" field.

Thanks

[#6]

Hi,

Please create a new field in the script mid(Week,2,2)  as WeekNo.

Use the following expression in the chart

Sum({<Report={'previous'},WeekNo={'$(=max($(WeekNo)-1))'}>} $(var_sales))

So if you select the W18-11 it shows the value for w17-11 . Now you can compare the Average Sales with  the Last Week Sales Value . Do let me know if you have any issues.

Regards,

Chakravarthy.

[#7]

This was exactly what I was looking for however, the test data I gave only included part of all the data, the problem is that I have several years in my data, so the results I am getting for the last week expression are always Week 53, because its the max week of all the data, is there a way to specify the Year as well since its contained inside the Week field as W15-11.

Edit: i have tried creating another field in the script as mid(Week,5,2) as YearNo and then using your expression with that clause but it does not seem to work when they are grouped, because if i use the max(YearNo) it sums all the units from the year 11, but if i use them together as by if(Report='previous' and YearNo=max(YearNo) and WeekNo=max(WeekNo), $(var_sales) it does not give me any expression, whereas if i take any of the max(WeekNo) or max(YearNo) it does give me results.

Many Thanks