Revenue between dates from text field (containing year-month)

[___]

I like to sum the total between a certain year 

Omzetmaand contains the value 2019-10 2019-09 etc 

I tried the following formula but it doesn't return anything 

=Sum({<MakeDate(left(Omzetmaand,4), num(right(Omzetmaand,2))) = {"$(='>=' & AddYears(monthsstart(1,Today()), -1) & '<=' & monthsstart(1,Today()))"}>}Omzet

[sunny_talwar]

How is Omzetmaand created in the script? Can you share the script where this is created?

[Stoyan_Terziev]

Hi Folks,

Sorry to step in but I had already started the reply 😉

So here is a bit of a highlight:

I think it's not possible to turn a non-numerical field into a numerical for the purpose of a set expression

Aka

Sum({<Num(TextField)={10}>} Value) will not work.

For the usage of >, < operators in set expression you need to use a numeric field.

Here is a test data sample to show you how you can fix the issue:

TestData:

Values:
LOAD
	Text(DateField) as DateFieldText
,	Date(Date#(DateField, 'D-M-YYYY')) as DateFieldDate
,	Value
;
Load * INLINE [
DateField, Value
9-10-2019,100
3-8-2019,150
6-9-2019,200
15-10-2019,140
22-9-2019,80
17-9-2019,100
];

Working expression:

=sum({<DateFieldDate={">=$(=AddYears(Date(Date#('01/10/2018','DD/MM/YYYY')),1))"}>} Value)

Not working expression:

=sum({<Date(Date#(DateFieldText, 'D-M-YYYY'))={">=$(=AddYears(Date(Date#('01/10/2018','DD/MM/YYYY')),1))"}>} Value)

I hope that helps!

Kind regards,

S.T. 

[Brett_Bleess]

Daniel, did Stoyan's post get you what you needed?  If so, do not forget to come back to the post and use the Accept as Solution button to give him credit for the help and let others know the explanation helped you get what you needed.  If you are still working on things, leave an update with what you need.

Regards,
Brett