Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Set Analysis help

I have this set

=sum({< [Date]={'$(vMaxDate)'}, Year={$(vMaxRevYear)}, Date(Date#([FiscalMonth],'MMM'),'MMM') = {'>= $(vForecastStartMonth)'} >} [AmountForecast] )

The third criteria is giving me and error

Date(Date#([FiscalMonth],'MMM'),'MMM') = {'>= $(vForecastStartMonth)'}

I can't seem to understand what is wrong. Any help?

2 Replies

Re: Set Analysis help

You are not allowed to use functions on the left hand side of set modifiers

=Sum({<[Date]={'$(vMaxDate)'}, Year={$(vMaxRevYear)}, Date(Date#([FiscalMonth],'MMM'),'MMM') = {'>= $(vForecastStartMonth)'} >} [AmountForecast] )

Why don't you create a field in the script

Month(Date#([FiscalMonth],'MMM'),'MMM') as FiscalMonth,

Num(Month(Date#([FiscalMonth],'MMM'),'MMM')) as FiscalMonthNum,

and then use one of these, preferrable FiscalMonthNum but not sure what vForecastStartMonth contains

=Sum({<[Date]={'$(vMaxDate)'}, Year={$(vMaxRevYear)}, FiscalMonth = {'>= $(vForecastStartMonth)'} >} [AmountForecast])

Valued Contributor II

Re: Set Analysis help

Try creating 'Date(Date#([FiscalMonth],'MMM'),'MMM')' in script as new field and use that field in set expression.