Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Partner
Partner

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

3 Replies
Highlighted

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

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

Highlighted
Partner
Partner

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

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. 

Highlighted
Digital Support
Digital Support

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

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.