How to exclude multiple date values in Set Analysis expression?

[#1]

I’m using the expression formula below to create a line chart for Year-Over-Year Revenue (by WeekNum) and NOT graph the remainder of the current year (whose value is 0% ).  However, I’d like to also omit WeekNum=1 from the graph.  I am new to expressions/set analysis and  have no idea how to begin doing this –  or if it’s even possible.  There is a MaxWeekNum variable in the data, but no MinWeekNum variable. 

if(Sum ({<Yr={'2011'},Wk_Num_Yr={'<=$(=MaxWeekNum)'}>}Revenue)=0,'',(Sum ({<Yr={'2011'},Wk_Num_Yr={'<=$(=MaxWeekNum)'}>}Revenue)/Sum ({<Yr={'2010'}>}Revenue))-1)

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

The following should work to exclude Wk_Num_Yr when equals to 1

Sum({< Yr = {201'}, Wk_Num_Yr = {'<=$(=MaxWeekNum)'} - {1} >} Revenue)

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#3]

That hid the first week, but now the chart is only showing data through the max week of the current year....I would still like to show all 52 weeks, because there is data there for last year.  How can I do this?

[Miguel_Angel_Baeyens]

Hi,

You will need to create a new field with both week number and year number in your calendar or date dimensions table,

Week(Date) & Right(Year(Date), 2) AS WeekYear, // it should return 0410 (week 4 year 2010)

so the expression is like

Sum({< Yr = {2010}, Wk_Num_Yr = {'>=$(=MinWeekYr)<=$(=MaxWeekYr)'} >} Revenue)

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica