previous week issue set analysis

[amien]

see attachment.

very simple pivot:

dimension : date, weeknumber, lastweeknumber

expression : sum(value)

expression is related to the date from the dimension

i want to add an additional expression which sums the values of the previous months. in some cases the values will be '0' because i dont have data on the previous month.

[johnw · Accepted solution]

You can't solve it with set analysis because a set is only generated once for the entire chart, not once per row.

You can handle it by generating and using an AsOf table:

AsOfWeek, WeekType, Week
...
10-2010, Last Week, 9-2010
10-2010, This Week, 10-2010
11-2010, Last Week, 10-2010
11-2010, This Week, 11-2010

dimension 1 = AsOfWeek
dimension 2 = WeekType
expression = sum(bedrag)

Move the dimension to the top of the chart. If you need this week and last week as separate expressions, such as if you wanted to calculate the change from last week to this week, use set analysis:

dimension = AsOfWeek
expression 1 = sum({<WeekType={'Last Week'}>} bedrag)
expression 2 = sum({<WeekType={'This Week'}>} bedrag)
expression 3 = colunn(2)-column(1) // for instance

[amien]

respect for your skills John.. Thanks again