Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

Re: Help needed on Max(Month)

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
Highlighted
Champion III
Champion III

Re: Help needed on Max(Month)

try this

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

Highlighted

Re: Help needed on Max(Month)

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)

Highlighted
Not applicable

Re: Help needed on Max(Month)

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.

Highlighted
Not applicable

Re: Help needed on Max(Month)

Hi Sunny,

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

Highlighted

Re: Help needed on Max(Month)

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

Highlighted
Champion
Champion

Re: Help needed on Max(Month)

Try like this at script?

Year*12+Month(Date) as MonthNumer

And then this expression:

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

Highlighted
Champion
Champion

Re: Help needed on Max(Month)

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

Highlighted
Not applicable

Re: Help needed on Max(Month)

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?

Highlighted
Not applicable

Re: Help needed on Max(Month)

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)