topic Wrong value return when adding date in set analysis in QlikView

Wrong value return when adding date in set analysis

EQ — Fri, 15 Jun 2018 04:32:10 GMT

Hi experts,

I have an issue when adding date into my set analysis expression, it just returns 0 but it is the wrong value. The expressions as below and i also attached the qvw file. Would a kind and enlightened please advise on my error, many thanks in advance.

sum({<TYPE = {FL}, DIM_DATE = {"> =$(= date ('2018/01/01' ) ) < =$(=date ( '2018/01/31' ))"} >}Total_Number)

sum({<TYPE = {FL}, DIM_DATE = {"> =$(= date ('2018/01/01' ) ) < =$(=date ( '2018/01/31' ))"} >}Net_Number)

Re: Wrong value return when adding date in set analysis

vishsaggi — Fri, 15 Jun 2018 15:49:41 GMT

Check if this is what you are expecting? Check attached.

Slight modification to your script.

LOAD *, Num#(DimDtID) AS DateID ;

LOAD DIM,

DIM_DATE,

Date#(DIM_DATE,'YYYY/M/DD h:mm:ss') AS DimDtID,

Net_Number,

Total_Number,

TYPE

FROM

(ooxml, embedded labels, table is Document_TB01);

LET vMinDate = Num(Date('2018/1/1'));

LET vMaxDate = Num(Date('2018/1/31'));

Expr used:

= sum({<TYPE = {FL}, DateID = {">=$(vMinDate)<=$(vMaxDate)"} >}Total_Number)

= sum({<TYPE = {FL}, DateID = {">=$(vMinDate)<=$(vMaxDate)"} >}Net_Number)

Re: Wrong value return when adding date in set analysis

vishsaggi — Fri, 15 Jun 2018 15:52:12 GMT

Always a good practice to use Integer values while filtering and comparing data in set analysis. That is how I do. It varies with individuals though. So i converted the dates to integers to filter on DateID, which also helps us to keep set analysis syntax simpler.

Re: Wrong value return when adding date in set analysis

sunny_talwar — Fri, 15 Jun 2018 16:05:26 GMT

You can also try this without changes to the script AS YOUR DIM_DATE seems to be saved in a timestamp format and not a date format

Sum({<TYPE = {FL}, DIM_DATE = {">=$(=TimeStamp ('2018/01/01'))<=$(=TimeStamp('2018/01/31'))"} >}Total_Number)

Re: Wrong value return when adding date in set analysis

EQ — Sat, 16 Jun 2018 00:01:13 GMT

Hi Vishwarath,

A very good suggestion, integer is better to do the filter, thank you very much for your help!

Re: Wrong value return when adding date in set analysis

EQ — Sat, 16 Jun 2018 00:02:54 GMT

Hi Sunny,

Yes, change it to TimeStamp() and verything works fine, thanks a lot!

Re: Wrong value return when adding date in set analysis

vishsaggi — Sat, 16 Jun 2018 05:02:06 GMT

Great. Close the thread marking correct/helpful responses accordingly.