Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
momajasa
New Contributor II

Strings within Strings in Set Analysis

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?

1 Solution

Accepted Solutions

Re: Strings within Strings in Set Analysis

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)

Capture.PNG

View solution in original post

8 Replies
MVP
MVP

Re: Strings within Strings in Set Analysis

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)

momajasa
New Contributor II

Re: Strings within Strings in Set Analysis

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

Thanks anyways

Re: Strings within Strings in Set Analysis

What are the variables and how you declare?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: Strings within Strings in Set Analysis

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

momajasa
New Contributor II

Re: Strings within Strings in Set Analysis

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

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)

Capture.PNG

View solution in original post

momajasa
New Contributor II

Re: Strings within Strings in Set Analysis

Thanks a lot Sunny, and everyone else

Re: Strings within Strings in Set Analysis

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