Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis Max Year, Month

Hello,

I'm trying to do something that seems relatively simple but i cannot get it to work. I want to calculate the sum of a value only for the maximum year and month of the current selection. This is the expression i'm using,

sum({$<YEAR = {$(=max(YEAR))}, MONTH = {$(=max(MONTH))}>} Stock_Value_CY)

I;ve broken the expression in its compnenets and although the expression for max year works, the expression for max month doesn't.

Anybody can see what i;m doing wrong?

14 Replies
Not applicable
Author

the 2 fields are YEAR and MONTH in capitals. They're both integer and numeric and when used in =max() function they give me the result i want.

I've pasted the MONTH field with no change.

The result i;m getting is 0, not '-'

A set analysis using only max(YEAR) brings me results while an expression with only max(MONTH) brings me 0.

PrashantSangle

Hi,

Can tell us

what value MONTH field contain?

and

What is output of =max(MONTH) in text box?

for Month field try like

=Month(max(MONTH))

or

=MonthName(max(MONTH))

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Not applicable
Author

create an extra field in your script as

num(Month(Yourdatefield)) as Month#,

and reload the application and write your expression as

sum({$<YEAR = {$(=max(YEAR))}, Month# = {$(=max(Month#))}>} Stock_Value_CY)

hope it will work

Not applicable
Author

Thank you all for taking the time to respond.

Anonymous
Not applicable
Author

Thank you for this.. I've been spending hours for the answer to my problem.