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

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

___ — Mon, 11 Nov 2019 16:35:41 GMT

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

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

sunny_talwar — Mon, 11 Nov 2019 16:53:08 GMT

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

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

Stoyan_Terziev — Mon, 11 Nov 2019 17:09:44 GMT

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.

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

Brett_Bleess — Thu, 14 Nov 2019 14:35:09 GMT

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