Issue in set expression

[#1]

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

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

I'm having issue with the underlined part.

Regards,

Adi

[sunny_talwar]

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.

[swuehl]

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

[sunny_talwar]

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

[#5]

Hi Sunny,

It worked but I see 114804 instead of 124185.

[swuehl]

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.

[sunny_talwar]

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

[sunny_talwar · Accepted solution]

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

[#9]

Stefan, it worked. Thanks a ton.