Evaluation of date format in set expression

[rik_qlik]

Hi,

i don't understand very well the behavior of the date format inside a set expression. I was think in a Date field the evaluation is made on the number presentation and not on the text presentation.. or not?

In the attached file (TEST.qvw) i try to make a set expression with the Date field but it seems to works only if the Date format is the same of the S.O. as you can see:

The set expression is the same for the two table: Only({<DATE={'$(=Max(DATE))'}>} STOCK_VALUE)

but the Date format in the load script is a bit different as you can see in the data of the tables..

I didn't expect this behavior because i was thinking the evaluation of the equal (DATE={'$(=Max(DATE))'}) in the set expression is between the number presentation/value of the Date instead of the text presentation/date format..

Somebody have some tips for me please?

Messaggio modificato da Riccardo Centomo

Messaggio modificato da Riccardo Centomo

[rik_qlik]

Hi Sunny, i'm trying to go ahead to my comprehension of the advanced search in the set analysis and i create the "TEST 2.qvw" where i evolve the expression to reach multiple condition, for example where the date are only on Friday.

So the first expression i formulate was:

FirstSortedValue({<DATE_={">=$(=Date(WeekStart(Max(DATE_),-1))) <=$(=Date(WeekEnd(Max(DATE_))))"}, DATE_={"$(=WeekDay(DATE_))='ven'"}>} STOCK_VALUE_, -DATE_)

but not work because the expansion dollar is wrong.

I try to analyze the following expression:

Only({<DATE_={"$(=Date(DATE_,'WWW'))='ven'"}>} DATE_)      [wrong]

Only({<DATE_={"$(=Date(DATE_,'WWW')='ven')"}>} DATE_)      [wrong]

Only({<DATE_={"=Date(DATE_,'WWW')='ven'"}>} DATE_)          [ok]

Only({<DATE_={"=Max(Date(DATE_,'WWW')='ven')"}>} DATE_)  [ok]

but i don't understand why the expansion dollar doesn't work inside the search of the set analysis while advanced search in the set analysis work also with "Max(Date(.." formulas..

So finally i understood the right expression was:

FirstSortedValue({<DATE_={">=$(=Date(WeekStart(Max(DATE_),-1))) <=$(=Date(WeekEnd(Max(DATE_))))"}, DATE_={"=Date(DATE_,'WWW')='ven'"}>} STOCK_VALUE_, -DATE_)

Now go ahead and keep only the "CODE" dimension and so my purpose is to find the value stock of the nearest Friday and the expression now is:

Only({<DATE_={"$(=Max({<DATE_={"=Date(DATE_,'WWW')='ven'"}>} DATE_))"}>} STOCK_VALUE_)

so in this case i have to use an advanced search, without the dollar expansion, nested inside the "Max()" formula that need to use the expansion dollar to be evaluated..

I think i reach my purpose but i don't understand very well where i have to use search with expansion dollar and more important how can i use advanced search in set analysis..

[sunny_talwar]

I think this blog (A Primer on Set Analysis) by HIC will be a good blog to read and understand what set analysis can be used in which scenario.

Best,

Sunny

[rik_qlik]

Thank you Sunny, i have found and read also the other blog of HIC about set analysis, but unfortunately i don't have found anything specific about the use of the advanced search in the set analysis..