WeekName Set Analysis

[#1]

Hi,

I've been through the forum posts re Set Analysis and they've helped me solve a problem or two but I can't find an answer to this one re weekname analysis

I have a Pivot Table Chart that I would like to load only the last 6 weeks worth of data. I load weekname from the Date field in the source files (Date format is DD/MM/YYYY only)

WeekName ( Date) as Week,

I use this in the Expression:

sum({<Week={$(=Week-1)}>} Total_Billable)

Viewing the table I can see this translates into

sum({<Week={40545}>} Total_Billable).

But it presents no data in the column

I have seen this problem before and fixed it (when looking at a similar table for Days) by doing the below:

sum({<date={'$(=date(date-1,'DD/MM/YYYY'))'}>} Total_Billable)

But I can't seem to do the same thing for WeekName. As usual it's probably something obvious but I can't seen to achieve it.

Can you please suggest how I could get over this

Thanks

John

[Miguel_Angel_Baeyens]

Hello John,

First of all, I'd create a new field in your calendar using Week(Field) to the the number (usually 1-53). That 40545 is the date in numeric value in QlikView, and the result of WeekName() function is a string formatted YYYY/WW so first you will need to format your week field

sum({< date = {'>=$(=WeekStart(Max(date), -6))'} >} Total_Billable)

Im ay be missing some parentheses. This expression is filtering for dates greater than or equal to the week corresponding to the maximum date loaded (in my example, change as you wish) six weeks backwards.

Hope this helps

[#3]

Miguel - thanks for the fast reply.

What you have provided works and shows me a total for the last six weeks. but I think I didn't explain my problem correctly.

I was loooking to present Current Week, Week-1, Week-2, Week-3, Week-4, Week-5 as columns. By defining Week( date) as you suggested I am able to get it working for the last several weeks, but not over this year to last year. As I load several years worth of data I've been using WeekName( Date) to show me a weeknumber as YYYY/WW.

Is there a way of using WeekName( Date) which I load (now) as WeekNm to present the current and last 5 weeks. It's that bit that I'm having problems with.

Thanks

[Miguel_Angel_Baeyens]

I'm probably missing something but using the same expression and setting "WeekNm" as dimension it will display the amount aggregated by week numbers. In a pivot table you can move the dimension to fit horizontal instead of vertical.

Hope this helps