Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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])
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.
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])
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])
Hi Sunny,
It worked but I see 114804 instead of 124185.
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.
Ya I think I missed the >= in my first expression
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])
Stefan, it worked. Thanks a ton.