topic Problem with set analysis formula in QlikView

Problem with set analysis formula

Thu, 24 Feb 2011 11:30:38 GMT

I have the following Set analysis formula which works as expected :

=sum({<FromCurrency={'$(vCURR)'},Type = {'Forecast'},ForecastEntryPeriod={$(LastForecast)}>} GBPForecastRevenue *PeriodAverageRate)

I am looking to add futther set analysis to the above command that will filter the returned data further so that only entries with a FYP field greater or Equal to the variable vFinanceYearPeriod are selected

FYP={">= $(vFinanceYearPeriod)"}

When I enter this as part of the formula

=sum({<FromCurrency={'$(vCURR)'},Type = {'Forecast'},ForecastEntryPeriod={$(LastForecast)},FYP={">= $(vFinanceYearPeriod)"}>} GBPForecastRevenue *PeriodAverageRate)

I keep getting an answer of 0. I have checked all of the variables and data individually and all appears OK. The issue only arises with this portion of the formula.

Am I using the correct syntax for >= calculations ?

ps the FYP and vFinanceYearPeriod fields are in the format yyyymmdd e.g. 20110506

Any help is greatly appreciated.

Problem with set analysis formula

Thu, 24 Feb 2011 11:58:57 GMT

Off the top of my head, I don't think you can do >= in set analysis. What you can do is

The way around this is to do analysis where you look for either ,(FYP> {'#'} or FYP= {'#'}) You might need to tweek that a little, but that's the concept.

Some goes for <>, you have to do =- or -= (can't remember which one) instead.

Problem with set analysis formula

Thu, 24 Feb 2011 12:14:13 GMT

Hi Ronnie,

be sure it´s possible to work with >= in a Set Analysis expression.

Paul,

try to control your expression. Create a table diagram. No lable for the expression. Now you can see if your variable contains the value you expect.

Or create a short example and post it here.

Good luck!

Rainer

Problem with set analysis formula

Thu, 24 Feb 2011 13:59:40 GMT

Rainer,

Thanks for your help. I have verified the information in the variable and it is correct. I have verified the FYP field using another chart and this is correct my issue is that when I try to use the >

Problem with set analysis formula

Thu, 24 Feb 2011 15:09:38 GMT

Paul,

see the attached example for more help.

Do you have still trouble upload a short example (*.qvw application will be the best).

I´m sure we can help and solve your issue.

Rainer

Problem with set analysis formula

Thu, 24 Feb 2011 15:28:41 GMT

I do this all the time: Year={">=$(=year(today()))"} and it works fine. Maybe if you try FYP={">=$(= $(vFinanceYearPeriod))"}?? Where '$(=' will evaluate your expression '$(vFinanceYearPeriod)'...

Good luck!

Cristian