topic Re: Help needed on Max(Month) in QlikView

Help needed on Max(Month)

Wed, 24 Feb 2016 11:56:02 GMT

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

Re: Help needed on Max(Month)

Chanty4u — Wed, 24 Feb 2016 11:58:30 GMT

try this

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

Re: Help needed on Max(Month)

sunny_talwar — Wed, 24 Feb 2016 11:59:50 GMT

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)

Re: Help needed on Max(Month)

Wed, 24 Feb 2016 12:02:01 GMT

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.

Re: Help needed on Max(Month)

Wed, 24 Feb 2016 12:03:25 GMT

Hi Sunny,

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

Re: Help needed on Max(Month)

sunny_talwar — Wed, 24 Feb 2016 12:04:28 GMT

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

Re: Help needed on Max(Month)

Anonymous — Wed, 24 Feb 2016 12:06:01 GMT

Try like this at script?

Year*12+Month(Date) as MonthNumer

And then this expression:

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

Re: Help needed on Max(Month)

Anonymous — Wed, 24 Feb 2016 12:07:59 GMT

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

Re: Help needed on Max(Month)

Wed, 24 Feb 2016 12:08:23 GMT

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?

Re: Help needed on Max(Month)

Wed, 24 Feb 2016 12:10:41 GMT

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)

Re: Help needed on Max(Month)

Anonymous — Wed, 24 Feb 2016 12:11:30 GMT

Yep, it will work as you have ignored Year & Month not Date

Re: Help needed on Max(Month)

sunny_talwar — Wed, 24 Feb 2016 12:11:57 GMT

Yes, I am certain it will and reason is by making selection in year and month, your max date will also change which will drive this expression

Re: Help needed on Max(Month)

sunny_talwar — Wed, 24 Feb 2016 12:13:55 GMT

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)

Re: Help needed on Max(Month)

Wed, 24 Feb 2016 12:16:31 GMT

Thanks Balraj and Sunny.

I will try these options.

Re: Help needed on Max(Month)

Anonymous — Wed, 24 Feb 2016 12:20:05 GMT

It seems either we did not get you or you are not able to explain.

Check these threads, will help you a lot..

Set Analysis for Rolling Periods

Set Analysis for certain Point in Time

Re: Help needed on Max(Month)

Colin-Albert — Wed, 24 Feb 2016 12:27:31 GMT

How has your Month data been created? It should be a dual field with both text and number parts.

Can you create a chart with Month as a Dimension and Num(Month) and text(Month) as Expressions.

If you dates are dual you will have both numeric and text values.

Re: Help needed on Max(Month)

Wed, 24 Feb 2016 12:53:38 GMT

Thanks Sunny. It worked for me.

Thanks Balraj and all for your valuable inputs.