topic Re: Set Analysis help in QlikView

Set Analysis help

Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Set Analysis help

sunny_talwar — Mon, 26 Sep 2016 19:42:06 GMT

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])

Re: Set Analysis help

neelamsaroha157 — Mon, 26 Sep 2016 19:43:27 GMT

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