Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Set analysis: last week of the previous year

Hello,

hopefully anyone can help me.

The following funktions works for the complete year except for the first week.

SUM({$<Calendar.Week={$(=Only(Calendar.Week)-1)}>} revenue)

How can I calculate the revenue for the last week of the previous year if the first week is chosen.

Many thanks in advance.

Tags (2)
1 Reply
MVP
MVP

Set analysis: last week of the previous year

If you're comparing this week to the previous week, I might move the logic to the script to tie each week to each previous week.

AsOf:
AsOfYear, AsOfWeek, WeekType, Calendar.Year, Calendar.Week
2011, 2, Current, 2011, 2
2011, 2, Previous, 2011, 1
2011, 1, Current, 2011, 1
2011, 1, Previous, 2010, 52
etc.

pivot table:
Dimension 1 = AsOfYear // left
Dimension 2 = AsOfWeek // left
Dimension 3 = WeekType // top
Expression = sum(revenue) // no set analysis required

If I didn't want a solution that complicated, I might use a WeekDate field defined as the first date in the week, then subtract 7 days from it with set analysis.

sum({<Calendar.WeekDate={$(=date(only(Calendar.WeekDate)-7))}>} revenue)

But you're probably looking for something like this:

if(only(Calendar.Week)>1
,sum({<Calendar.Week={$(=only(Calendar.Week)-1)}>} revenue)
,sum({<Calendar.Year={$(=only(Calendar.Year)-1)}
,Calendar.Week={$(=max({<Calendar.Year={$(=only(Calendar.Year)-1)}>} Calendar.Week)-1} revenue))

Community Browser