Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
EQ
Contributor III
Contributor III

Wrong value return when adding date in set analysis

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)

1 Solution

Accepted Solutions
vishsaggi
Champion III
Champion III

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)

View solution in original post

6 Replies
vishsaggi
Champion III
Champion III

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)

vishsaggi
Champion III
Champion III

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.

sunny_talwar

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)

EQ
Contributor III
Contributor III
Author

Hi Vishwarath,

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

EQ
Contributor III
Contributor III
Author

Hi Sunny,

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

vishsaggi
Champion III
Champion III

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