Max Month-Year issue

[davyqliks]

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.

[sunny_talwar]

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

[davyqliks]

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

[sunny_talwar]

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.

[davyqliks]

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 

[sunny_talwar]

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? 

[davyqliks]

Thank you for the advice,

When i tried this i got Dec 19 as expected.

Any suggestions, thanks a lot Sunny

Daniel

[sunny_talwar]

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

[davyqliks]

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 

[sunny_talwar]

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?