How to make Sum({<YearMonth={">=2019 July"}>} sales) working ?

[paulyeo11]

Hi All

I have below expression work fine :-

Sum({<YearMonth={">=2019"}>} sales)

I like modify the expression , so that it will filter all date > 2019 July.

Sum({<YearMonth={">=2019 July"}>} sales)

it does not work.

Can some one advise me.

Paul Yeo

[Stoyan_Terziev]

Hi Paul,

Whenever you'd like to use the > or < operators of a date function, you need to know that each date is a dual value (text and number).

For example: The MonthName function creates a dual value which is equal to the number of the first date of the month, also it is equal to your system's format for Month Year (Usually something like Feb 2019).

Here are two equations to help you understand dates:

And here is the expression you may be looking for:

=Sum({<ProperMonth={">=$(=MonthStart(MonthName(Date#('2019 Feb', 'YYYY MMM'))))"}>} Sales)

A prerequisite for this to work without issues is to convert your 'YearMonth' field into proper month on the back-end. Like this:

TestData:
LOAD
	Sales, 
	MonthName(Date#(YearMonth, 'YYYY MMM')) as ProperMonth
;
Load * INLINE [
Sales, YearMonth
1, 2019 Jan
2, 2019 Feb
3, 2019 Mar
4, 2019 Apr
5, 2019 May
6, 2019 Jun
];

That way you are enforcing it to evaluate the numeric value behind your fields.

I hope that helps!

Kind regards,

S.T.

[paulyeo11]

Hi Sir

Thank you very much for your sharing.

=Sum({<YearMonth={">=$(=MonthStart(MonthName(Date#('2019 Feb', 'YYYY MMM'))))"}>} sales)

I have try the above expression , but it return null value.

Paul

[Stoyan_Terziev]

Hi Paul,

Don't disregard the rest of the message I wrote.

This will only work if your field YearMonth is properly set on the back-end. When you are loading it, are you doing this:

Load ....

MonthName(YearMonth) as YearMonth

....

You can troubleshoot with this expression:

=Max(YearMonth)

 If it files back a null value ( - ), then you haven't set it properly since it is not numeric.

Kind regards

S.T.

[paulyeo11]

Hi Sir

I still cannot understand what you mean, may be i enclosed my QVW

[Stoyan_Terziev · Accepted solution]

Hi Paul

I just tested it and it works without any issues in your QVW. Attached is the screenshot, the expression and the QVW with formula.

=Sum({<YearMonth={">=$(=MonthStart(MonthName(Date#('2019 Feb', 'YYYY MMM'))))"}>} sales)

Kind regards,

S.T.

[paulyeo11]

Hi Sir

Thank you very much.

Paul