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

[sunny_talwar · Accepted solution]

Unfortunately, the comparison isn't based on just the number and the format matters. I had a similar question a while ago (Re: Set Analysis....Syntax for using Date‌). See if that discussion help you out.

[sunny_talwar]

With regards to the sample, to fix the table for TAB1, I changed the format of your date field from document properties to make them all have the same format.

The if statement expressions were fixed using these expressions

Sum(If(DATE=Max(TOTAL DATE),STOCK_VALUE))

Sum(If(DATE_ = Max(TOTAL DATE_),STOCK_VALUE_))

[rik_qlik]

Hi Sunny, thank you for the quick reply.

I'm reading the post you show to me to learn more about set analysis..

But i don't understand why in the if statement we have to use the Total and an aggregation function like the only() or the sum() so it can works. I'm not able to understand the error in the statement..

[rik_qlik]

Also my final purpose is to find, for every "CODE", the most recent "STOCK_VALUE" in the week = Week(Max(DATE))-1:

for LUX.MI the stock_value is 44,08 in date 05/08/2016

for FTSEMIB.MI the stock_value is 16.236,40 in date 04/08/2016

but i'm not able to find a set analysis solution or a if statement solution.

Sure i'm able to achieve the result by script using join and temporary tables for calculate it..

[sunny_talwar]

May be try this expression:

FirstSortedValue({<DATE= {"$(='>=' & Date(WeekStart(Max(DATE) , -1)) & '<=' & Date(WeekEnd(Max(DATE), -1)))"}>} STOCK_VALUE, -DATE)

[rik_qlik]

Hi Sunny, sorry but i was away..

i try your set expression and it's near to my goal but there is a problem: it show the same value of the filed "STOCK_VALUE" for every "CODE":

i expect 16.236,40 for FTSEMIB.MI..

Also i try this set expression:

FirstSortedValue({<DATE= {">= $(=Date(WeekStart(Max(DATE),-1))) <= $(=Date(WeekEnd(Max(DATE),-1)))"}>} STOCK_VALUE, -DATE)

but, whit this similar set expression, the result is null and i don't understand why..

[sunny_talwar]

Did you check this?

Expression used:

FirstSortedValue({<DATE= {"$(='>=' & Date(WeekStart(Max(DATE) , -1)) & '<=' & Date(WeekEnd(Max(DATE), -1)))"}>} STOCK_VALUE, -DATE)

[rik_qlik]

Oh sorry i have made a mistake using wrong field.. sorry!

In my expression the error was in the space after the <=

wrong expression: FirstSortedValue({<DATE= {">= $(=Date(WeekStart(Max(DATE),-1))) <= $(=Date(WeekEnd(Max(DATE),-1)))"}>} STOCK_VALUE, -DATE)

right expression: FirstSortedValue({<DATE= {">= $(=Date(WeekStart(Max(DATE),-1))) <=$(=Date(WeekEnd(Max(DATE),-1)))"}>} STOCK_VALUE, -DATE)

The second expression work like yours

[sunny_talwar]

Awesome