topic Re: Issue in set expression in QlikView

Issue in set expression

Mon, 29 Feb 2016 20:56:35 GMT

Hi,

The following expression returns 222037 which is right.

=sum({<[Forecast Metric]={"Orig Loans/Advances # - New","Orig Loans/Advances # - Repeat"},[Report Month]={"$(=Date(YearStart([Report Month]),'MMM YYYY'))"}>}[Forecast Values2])

I want to add one more condition to my set expression

=sum({<[Forecast Metric]={"Orig Loans/Advances # - New","Orig Loans/Advances # - Repeat"},[Report Month]={"$(=Date(YearStart([Report Month]),'MMM YYYY'))"},MY={"$(=Year(Max({<Flag={'1'}>}MY)))"}>}[Forecast Values2])

After adding the condition, I'm getting '0'.

I tried reading value of

=Year(Max({<Flag={'1'}>}MY)), it's 2016 which is also right.

My original expression with all the 2016 months in MY selected returns, 124185 which is right.

Can someone help me with this expression

I'm having issue with the underlined part.

Regards,

Adi

Re: Issue in set expression

sunny_talwar — Mon, 29 Feb 2016 21:00:15 GMT

MY is MonthYear field and you are equating it to just a Year which is never going to work. You will have to equate it either to a year field or a range of YearStart to YearEnd in the MMM-YYYY format.

Re: Issue in set expression

swuehl — Mon, 29 Feb 2016 21:00:30 GMT

You are trying to assign a year value to a field that hold month-year values, so the values are not compatible.

Maybe try something like

=sum({<[Forecast Metric]={"Orig Loans/Advances # - New","Orig Loans/Advances # - Repeat"},[Report Month]={"$(=Date(YearStart([Report Month]),'MMM YYYY'))"},MY={"*-$(=Year(Max({<Flag={'1'}>}MY)))"}>}[Forecast Values2])

Re: Issue in set expression

sunny_talwar — Mon, 29 Feb 2016 21:01:43 GMT

May be this:

=Sum({<[Forecast Metric]={"Orig Loans/Advances # - New","Orig Loans/Advances # - Repeat"},[Report Month]={"$(=Date(YearStart([Report Month]),'MMM YYYY'))"},MY={"$(='>' & Date(YearStart(Max({<Flag={'1'}>}MY)), 'MMM-YYYY') & '<=' & Date(YearEnd(Max({<Flag={'1'}>}MY)), 'MMM-YYYY'))"}>}[Forecast Values2])

Re: Issue in set expression

Mon, 29 Feb 2016 21:35:08 GMT

Hi Sunny,

It worked but I see 114804 instead of 124185.

Re: Issue in set expression

swuehl — Mon, 29 Feb 2016 21:41:05 GMT

Try to use a 'larger equal' in the first part of the search expression:

MY={"$(='>=' & Date(YearStart(Max({<Flag={'1'}>}MY)), 'MMM-YYYY')...

Or try the wildcard search I've suggested above.

Re: Issue in set expression

sunny_talwar — Mon, 29 Feb 2016 21:42:35 GMT

Ya I think I missed the >= in my first expression

Re: Issue in set expression

sunny_talwar — Mon, 29 Feb 2016 21:43:25 GMT

I guess as Stefan pointed out, try this:

=Sum({<[Forecast Metric]={"Orig Loans/Advances # - New","Orig Loans/Advances # - Repeat"},[Report Month]={"$(=Date(YearStart([Report Month]),'MMM YYYY'))"},MY={"$(='>=' & Date(YearStart(Max({<Flag={'1'}>}MY)), 'MMM-YYYY') & '<=' & Date(YearEnd(Max({<Flag={'1'}>}MY)), 'MMM-YYYY'))"}>}[Forecast Values2])

Re: Issue in set expression

Mon, 29 Feb 2016 21:43:30 GMT

Stefan, it worked. Thanks a ton.