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

Help needed on Max(Month)

Hi All,

Hope all are doing good...

I need your help to solve one of the issue that i am facing.

I have a date field in DD/MM/YYYY and i have retrieved Month and Year from that field.

Now the issue is that when i am writing the Max(Month) expression to select Maximum month it is returning Month number instead of Month Name.As a result the value is not getting selected in the month field.

However I have used Month(Max(Date)) to get maximum month and i am able to select maximum month with that expression.

But at the same time for YTD calculation i have used Sum({<Year={"$(=Max(Year))"},Month={"$(=Month(Max(Date)))"}>}Sales) which returning '0'.

Please help me with the correct approach to resolve the issue.

Thanks in advance.

Best Regards,

Nani

1 Solution

Accepted Solutions
sunny_talwar

It seems you are looking for a YTD expression and not MTD. For YTD, try this:

Sum({<Date = {"$(='>=' & Date(YearStart(Max(Date)), 'DD/MM/YYYY') & '<=' & Date(MonthEnd(Max(Date)), 'DD/MM/YYYY'))"}Year = ,Month = >}Sales)

View solution in original post

16 Replies
Chanty4u
MVP
MVP

try this

= Sum({<Year={'$=Max(Year)'},Month={'$(=Month(Max(Date)))'}>}Sales)

sunny_talwar

Why don't you do this with the dates instead?

Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date)), 'DD/MM/YYYY') & '<=' & Date(MonthEnd(Max(Date)), 'DD/MM/YYYY'))"}Year = ,Month = >}Sales)

Not applicable
Author

Hi Suri,

Thanks for response.

I have used Sum({<Year={"$(=Max(Year))"},Month={"<=$(=Month(Max(Date)))"}>}Sales) expression. But no luck it is returning 0.

Not applicable
Author

Hi Sunny,

Thanks for the response. But we dont have Date filters on the dashboard.

sunny_talwar

You don't need to have date filters. The expression will still work based on selections you make in Year and Month fields. Try it out

Anonymous
Not applicable
Author

Try like this at script?

Year*12+Month(Date) as MonthNumer

And then this expression:

=Sum({<Month={'$(=Max(MonthNumer))'} }>}Sales)

Anonymous
Not applicable
Author

As you said:

Now the issue is that when i am writing the Max(Month) expression to select Maximum month it is returning Month number instead of Month Name.



When you use Max(Month), max() returns integer only, will not return month name

Not applicable
Author

Sure sunny i will try. But my question is we have ignored the selections in Yearanf month in the expression right.

Do you still think that this expression will work out?

Not applicable
Author

Yeah i realized and used Month(Max(Date)) to get the Max month. But that is not working as expected when i am using for YTD with the below expression

Sum({<Year={"$(=Max(Year))"},Month={"<=$(=Month(Max(Date)))"}>}Sales)