topic Re: How to exclude multiple date values in Set Analysis expression? in QlikView

How to exclude multiple date values in Set Analysis expression?

Thu, 07 Jul 2011 22:44:38 GMT

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)

Re: How to exclude multiple date values in Set Analysis expression?

Miguel_Angel_Baeyens — Thu, 07 Jul 2011 23:22:11 GMT

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

Re: How to exclude multiple date values in Set Analysis expression?

Fri, 08 Jul 2011 15:43:26 GMT

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?

Re: How to exclude multiple date values in Set Analysis expression?

Miguel_Angel_Baeyens — Fri, 08 Jul 2011 16:42:15 GMT

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