topic Re: Strings within Strings in Set Analysis in QlikView

Strings within Strings in Set Analysis

Anonymous — Wed, 23 Aug 2017 07:16:56 GMT

Hello everyone,

I am solving examples from the "QlikView for Developers Cookbook" and stuck at an example using set Analysis to define the selected date range, as the provided Syntax seems to be incorrect:

Sum({$<SalesDate={'>=$(=Date(vFromDate, 'YYYY-MM-DD'))<=$(=Date(vToDate, 'YYYY-MM-DD'))'}>} Value)

One cannot use multiple ' ' within an Expression, afaik, but I don't know how to solve this Situation... using " " for the outter and ' ' for the inner strings does not work either ... you guys have any ideas?

Re: Strings within Strings in Set Analysis

tresB — Wed, 23 Aug 2017 09:20:38 GMT

Mahdi Jafari wrote:

.. " " for the outter and ' ' for the inner strings does not work either ..

that should actually work. Try like:

Sum({$<SalesDate={">=$(=Date(vFromDate, 'YYYY-MM-DD')) <=$(=Date(vToDate, 'YYYY-MM-DD'))"}>} Value)

Re: Strings within Strings in Set Analysis

Anonymous — Wed, 23 Aug 2017 11:07:10 GMT

I agree, but I can't get it to work...

Thanks anyways

Re: Strings within Strings in Set Analysis

Anil_Babu_Samineni — Wed, 23 Aug 2017 11:39:26 GMT

What are the variables and how you declare?

Re: Strings within Strings in Set Analysis

sunny_talwar — Wed, 23 Aug 2017 11:42:58 GMT

Also, have you made sure that your SalesDate field is in this format -> YYYY-MM-DD?

Re: Strings within Strings in Set Analysis

Anonymous — Wed, 23 Aug 2017 12:13:57 GMT

Here is the whole script:

LOAD * INLINE [
Country, Value, SalesDate
USA, 12, 2013-01-04
USA, 14.5, 2013-02-07
USA, 6.6, 2013-03-03
USA, 4.5, 2013-04-11
USA, 7.8, 2013-05-19
USA, 9.4, 2013-06-22
UK, 11.3, 2013-01-31
UK, 10.1, 2013-02-01
UK, 3.2, 2013-03-21
UK, 5.6, 2013-04-15
UK, 3.9, 2013-05-12
UK, 6.9, 2013-06-06
];

Let vFromDate=Floor(Date#('2013-01-01', 'YYYY-MM-DD'));
Let vToDate=Floor(Date#('2013-06-30', 'YYYY-MM-DD'));

...

I have double-checked everything and couldn't find out why this doesn't work properly.

Re: Strings within Strings in Set Analysis

sunny_talwar — Wed, 23 Aug 2017 15:21:57 GMT

It seems to be working (with some changes)

1) Your variable date range included all the observations... so in order to test the set analysis, I changed the vFronDate to 2013-04-01

2) Used SET DateFormat='YYYY-MM-DD'; in the environmental variable to make sure your SalesDate field is read as a date field by QlikView

SET DateFormat='YYYY-MM-DD';

LOAD * INLINE [

Country, Value, SalesDate

USA, 12, 2013-01-04

USA, 14.5, 2013-02-07

USA, 6.6, 2013-03-03

USA, 4.5, 2013-04-11

USA, 7.8, 2013-05-19

USA, 9.4, 2013-06-22

UK, 11.3, 2013-01-31

UK, 10.1, 2013-02-01

UK, 3.2, 2013-03-21

UK, 5.6, 2013-04-15

UK, 3.9, 2013-05-12

UK, 6.9, 2013-06-06

];

Let vFromDate=Floor(Date#('2013-04-01', 'YYYY-MM-DD'));

Let vToDate=Floor(Date#('2013-06-30', 'YYYY-MM-DD'));

Expression

Sum({$<SalesDate={">=$(=Date(vFromDate, 'YYYY-MM-DD'))<=$(=Date(vToDate, 'YYYY-MM-DD'))"}>} Value)

Re: Strings within Strings in Set Analysis

Anonymous — Thu, 24 Aug 2017 09:08:32 GMT

Thanks a lot Sunny, and everyone else

Re: Strings within Strings in Set Analysis

sunny_talwar — Thu, 24 Aug 2017 15:32:12 GMT

No problem... if you got what you wanted, I would request you to close this thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny