topic Re: Max Year and Latest Month in QlikView

Max Year and Latest Month

Anonymous — Mon, 23 Mar 2015 21:24:25 GMT

I am trying to display sum of payments for the current year and latest month in my set of data. I have checked several threads on the discussion board and none have provided a solution. My set analysis is currently:

=sum({<PayYear={'$(=Max(PayYear))'}, PayMonth={'$(=Max(PayMonth))'}>}[DIST AMT USD])

Sum keeps coming out to 0 ... What am I missing?

Re: Max Year and Latest Month

ramoncova06 — Tue, 24 Mar 2015 03:15:47 GMT

Try sum({<PayYear={'=$(=Max(PayYear))'}, PayMonth={'=$(=Max(PayMonth))'}>}[DIST AMT USD])

I would suggest as creating them as variables

Re: Max Year and Latest Month

danieloberbilli — Tue, 24 Mar 2015 03:31:16 GMT

or try

sum({<PayYear={"=$(=Max(PayYear))"}, PayMonth={"=$(=Max(PayMonth))"}>}[DIST AMT USD])

Re: Max Year and Latest Month

robert_mika — Tue, 24 Mar 2015 03:31:44 GMT

The issue you are facing here that if your Max month is let say 8 but the year does not reach that point you will get zero.

Example

Year,Month

2015,3

2014,5

The Max Year is 2015, the MaxMonth is 8 but MaxYear&MaxMonth =0.

I can not give you answer but you need somehow first extract the array of 2015 and then based on that look for Max of this year.

This is easy in Excel where you can use array formulas The results is 2+22+26=52

Re: Max Year and Latest Month

hariprasadqv — Tue, 24 Mar 2015 03:35:16 GMT

Hi,
take max(year), min
(month) of the years.

Re: Max Year and Latest Month

sagarkharpude — Tue, 24 Mar 2015 03:56:35 GMT

Try this. its working fine

Month should be in number format

=Sum({<PayYear={$(=max(PayYear))},PayMonth={$(=max(PayMonth))}>}[DIST AMT USD])

Re: Max Year and Latest Month

mukesh24 — Tue, 24 Mar 2015 04:11:40 GMT

Hi David,

Simply calculated Max Year & Max Month, if u directly write max month in expression as per suggested by Robert u will not get proper result.

Declare variable as :-

vMaxYear = max(Year)

vMaxMonth = max({<Year = {"$(vMaxYear)"}>}Month)

use above variable in expression as

=sum({<PayYear={"$(vMaxYear )"}, PayMonth={"$(vMaxMonth)")'}>}[DIST AMT USD])

Try

Re: Max Year and Latest Month

Anonymous — Tue, 24 Mar 2015 04:22:11 GMT

Do you have a date field? If so just do max(date). It's the same as max year and max month.