Wrong value return when adding date in set analysis

[EQ]

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)

[vishsaggi · Accepted solution]

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]

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]

Hi Vishwarath,

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

[EQ]

Hi Sunny,

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

[vishsaggi]

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