Strings within Strings in Set Analysis

[Anonymous]

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?

[tresesco]

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)

[Anonymous]

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

Thanks anyways

[Anil_Babu_Samineni]

What are the variables and how you declare?

[sunny_talwar]

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

[Anonymous]

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 · Accepted solution]

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)

[Anonymous]

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