topic Which one is Max Month up to data available in QlikView

Which one is Max Month up to data available

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

We have the fields, Sales ,Year and Month.

I have to display the max year max month i.e max month obviously Dec but I have data up to March and for remaining months there is zeros. In set analysis march should take as max month. Can any help me.

Re: Which one is Max Month up to data available

sarvesh — Thu, 16 Feb 2017 11:43:22 GMT

Hi..Kumar,

First you deside calendar either April - March or January - December, then you can get appropriate data as per requirement.

Regard's

Sarvesh Srivastava

Re: Which one is Max Month up to data available

sunny_talwar — Thu, 16 Feb 2017 11:54:58 GMT

The below expression should give you March if March 2017 if that's the maximum month where you have Sum(Sales) > 0

Max({<MonthYear = {"=Sum(Sales) > 0"}>}Month)

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

Re: Which one is Max Month up to data available

sdmech81 — Thu, 16 Feb 2017 11:59:35 GMT

May be like this:

=month(max({sale-={'0'}}date))

Attach data if not helped..

Sachin

Re: Which one is Max Month up to data available

Chanty4u — Thu, 16 Feb 2017 12:05:42 GMT

or may be

=FirstSortedvalue(Month,-Sales)

Re: Which one is Max Month up to data available

sunny_talwar — Thu, 16 Feb 2017 12:07:13 GMT

If the data looks like this

Jan - 50

Feb - 40

Mar - 30

Apr - 0

...

Your expression will give Jan. I don't think this is what is needed here.

Re: Which one is Max Month up to data available

sdmech81 — Thu, 16 Feb 2017 12:30:35 GMT

put this into ur set as modifier and try ..it should work..

Sachin

Re: Which one is Max Month up to data available

Thu, 16 Feb 2017 13:53:03 GMT

Hi Guys,

Thanks to every one,

@ Sarvesh -- Calendar Jan - Dec

@ Sunny -- I am getting exact month number by using your expression, This is nice any way need to get values for that particular month.

@Sachin -- I used your expressions but not getting expected results.

@sunny

Please guide me if I done any mistake

Max({<Month = {"=Sum({<Year = {$(=Max(CalYear))}>}Sales) > 0"}>}Month)

Here Month is coming from fact table and Calyear from iland master calendar, master calendar month is Calmonth.

Re: Which one is Max Month up to data available

sunny_talwar — Thu, 16 Feb 2017 14:11:28 GMT

Would you be able to share a sample to see your issue?