Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
See why Qlik was recognized for the seventh year in a row – and discover how we can help you tackle your data integration challenges. Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
momajasa
Contributor II
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
sunny_talwar

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
tresesco
MVP
MVP

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
Contributor II
Contributor II
Author

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

Thanks anyways

Anil_Babu_Samineni

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)
sunny_talwar

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

momajasa
Contributor II
Contributor II
Author

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. 

sunny_talwar

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

momajasa
Contributor II
Contributor II
Author

Thanks a lot Sunny, and everyone else

sunny_talwar

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