Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Issue in set expression

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

8 Replies
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
MVP
MVP

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

Not applicable
Author

Hi Sunny,

It worked but I see 114804 instead of 124185.

swuehl
MVP
MVP

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

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

Not applicable
Author

Stefan, it worked. Thanks a ton.