topic Re: Max Month-Year issue in QlikView

Max Month-Year issue

davyqliks — Sat, 16 Nov 2024 19:29:51 GMT

I wonder if you might be able to help with the following.

When I have the below:

Sum({<[Year]={'$(=num(Max([Year])))'}>} HKD_Price)

I get a correct result

But when trying the same with Month-Year I have no return

Sum({<[Month-Year]={'$(=num(Max([Month-Year])))'}>} HKD_Price)

Do you have any idea why this may be returning a blank?

I have also tried the following with – as the return

Sum( {< [Month-Year] = { '$(=Max([Month-Year])' } > } HKD_Price )

Note, If I sum HKD_Price and select all Month -Year for 2019 I am getting the correct return

My Date Value is called [Voucher Date]

Thank you As always, any advise would be much appreciated.

Re: Max Month-Year issue

sunny_talwar — Mon, 09 Dec 2019 13:10:40 GMT

How is [Month-Year] created in the script?

Re: Max Month-Year issue

davyqliks — Mon, 09 Dec 2019 13:19:39 GMT

Hi Sunny,

Thank you. Here is the import script for Month-Year

[Voucher Date] as [%VoucherDate],
Date(monthstart([Voucher Date]), 'MMM-YYYY') as [Month-Year],
Year([Voucher Date]) as Year,
Month([Voucher Date]) as Month,
Day([Voucher Date]) as Day,

Thanks for your time.

Daniel

Re: Max Month-Year issue

sunny_talwar — Mon, 09 Dec 2019 13:22:35 GMT

Try this in that case

Sum({<[Month-Year] = {"$(=Date(Max([Month-Year]), 'MMM-YYYY'))"}>} HKD_Price)

Keeping the set analysis modifier's format same as the format of the field is important here... within if it doesn't matter, but within set analysis it does.

Re: Max Month-Year issue

davyqliks — Mon, 09 Dec 2019 13:41:10 GMT

Hi,

Thank you Sunny,

All looks ok, but still returns a blank.

Sum({<[Month-Year] = {"$(=Date(Max([Month-Year]), 'MMM-YYYY'))"}>} HKD_Price)

Its an odd one, as mentioned if i just Sum HKD_Price and select 2019 Month Years i get the correct return.

Thank you for trying.

Regards

Daniel

Re: Max Month-Year issue

sunny_talwar — Mon, 09 Dec 2019 13:49:27 GMT

Daniel -

Have you tried putting this

=Date(Max([Month-Year]), 'MMM-YYYY')

in a text object to see what values does it give? Is it giving the right Max Month Year that you would expect or is it giving a future Month Year where there is no HKD_Price available?

Re: Max Month-Year issue

davyqliks — Mon, 09 Dec 2019 14:05:54 GMT

Thank you for the advice,

When i tried this i got Dec 19 as expected.

Any suggestions, thanks a lot Sunny

Daniel

Re: Max Month-Year issue

sunny_talwar — Mon, 09 Dec 2019 14:09:59 GMT

You get Dec 19 without selection in Year field? And what happens when you select the Year 2019? You still see Dec 2019?

Re: Max Month-Year issue

davyqliks — Mon, 09 Dec 2019 14:28:56 GMT

Thats right,

I am getting Dec-2019 in the text box before and after the year 2019 was selected when using =Date(Max([Month-Year]), 'MMM-YYYY')

Thanks

Daniel

Re: Max Month-Year issue

sunny_talwar — Mon, 09 Dec 2019 14:32:59 GMT

Very strange that you get the same value in the text box object, but it gives a different result when you use it in set analysis... Would you be able to share a qvw sample to where we can see the issue?

Re: Max Month-Year issue

davyqliks — Mon, 09 Dec 2019 14:59:51 GMT

HI,

While making you a sample i realised why i am getting the 0.00 return.

I use a currency conversion to get my HKD_Price.

These are not confirmed until the end of the month and so no currency conversion for DEC.

When i sum(HKD_Price) for Dec the return is blank.

Therefor, this is affecting the return value for max.

Is there a way of adding to the syntax something to state max complete Month-Year, so i am getting the value for Nov 2019 until Dec 2019 is past the 31st?

this is working when selecting jan 19 to nov 19

Sum({<[Month-Year] = {"$(=Date(Max([Month-Year]), 'MMM-YYYY'))"}>} HKD_Price)

I hope that's clear, thanks again sunny.

Daniel

Re: Max Month-Year issue

sunny_talwar — Mon, 09 Dec 2019 15:07:04 GMT

May be this

Sum({<[Month-Year] = {"$(=Date(Max({<HKD_Price *= {[*]}>}[Month-Year]), 'MMM-YYYY'))"}>} HKD_Price)

Re: Max Month-Year issue

davyqliks — Mon, 09 Dec 2019 15:15:52 GMT

Hi Sunny,

Thats got it, Shows the HKD_Price for the last completed Month (Nov)

Thank you so much for your time, expertise and patience, its really appreciated.

Kind Regards

Daniel