Problem with set analysis

[ttal7878]

Hi,

Can anyone explain,  why the information is not displayed in table "Display Unique"?

I have an ID that meets all the conditions I defined in my set Analytics and is not yet displayed in the table "Display Unique".

It's problematic for me to upload my real model,

and I can't imitate the problem in an example model, So I'll try to explain with picture from my model.

I have a table "Display Unique" which counts the unique's according to the following parameters:

Lead_In_TMS={"Yes"},

ActionTypeName={"Lead"},

Source={"Online"}

Sale_Date={">=$(VMinDateIgnoreActionLeadTMSSource)<=$(VMaxDateIgnoreActionLeadTMSSource)"}>}

and ignore the following parameters:

Lead_In_TMS=

ActionTypeName=

Source=

The variables are calculated as follows:

VMinDateIgnoreActionLeadTMSSource=

=Timestamp(min({<ActionTypeName=,Lead_In_TMS=,Source=>}Sale_Date),'YYYY-MM-DD hh:mm')

VMaxDateIgnoreActionLeadTMSSource=

=Timestamp(max({<ActionTypeName=,Lead_In_TMS=,Source=>}Sale_Date),'YYYY-MM-DD hh:mm')

I'am using the following Set Analysis to count the unique's:

=count(DISTINCT {<ActionTypeName=,Lead_In_TMS=,Source=,

Lead_In_TMS={"Yes"},ActionTypeName={"Lead"},Source={"Online"},

Sale_Date={">=$(VMinDateIgnoreActionLeadTMSSource)<=$(VMaxDateIgnoreActionLeadTMSSource)"}>}

Unique)

What do I miss?

[ttal7878]

They are in the same date, because column Sale_Date indicates the registration date of the Unique.

Because I have filtered one Unique, the start date is equal to the end date

[sunny_talwar]

The problem is that since this is timestamp we are getting into Rounding Errors and even if you have something as precise as 2017-09-27 16:58 in your database, it may not show up because of rounding errors....

May be try this

VMinDateIgnoreActionLeadTMSSource=

=Timestamp(min({<ActionTypeName=,Lead_In_TMS=,Source=>}Sale_Date) - MakeTime(0, 0, 1),'YYYY-MM-DD hh:mm')

VMaxDateIgnoreActionLeadTMSSource=

=Timestamp(max({<ActionTypeName=,Lead_In_TMS=,Source=>}Sale_Date) + MakeTime(0, 0, 1),'YYYY-MM-DD hh:mm')

[ttal7878]

still not working,

[sunny_talwar]

Does your Sale_Date include seconds or is it floored at minutes?

[ttal7878]

Its format from the DB is '2017-09-28 08:00:12.290'
And in the script I used
  Timestamp (Sale_Date, 'YYYY-MM-DD hh: mm') as Sale_Date

[sunny_talwar]

And you don't care about the seconds and microseconds part? Try this

Timestamp(Floor(Sale_Date, 1/1440), 'YYYY-MM-DD hh: mm') as Sale_Date

[ttal7878]

still not working,

I don't use seconds and microseconds part.

I have Created a new field in the script with what you've send (Sale_Datenew)

and I changed the variables to:

=Timestamp(min({<ActionTypeName=,Lead_In_TMS=,Source=>}Sale_Datenew) - MakeTime(0, 0, 1),'YYYY-MM-DD hh:mm')

=Timestamp(max({<ActionTypeName=,Lead_In_TMS=,Source=>}Sale_Datenew) + MakeTime(0, 0, 1),'YYYY-MM-DD hh:mm')

and also I use it in the set analysis

And after that did't work, I also changed the triggers which affecting the date range to use the field Sale_Datenew

[sunny_talwar]

Out of ideas... may be someone else can offer help

[sudeepkm]

I know you have already tried so many options with Sunny's help. Could you please check a number format or the Julian format of the date and use that field in the Set analysis. Still not sure if Date field is causing the issue.

[lorenzoconforti]

Can you check how the TimestampFormat is set in your loading script? Does it match with what you see in the Sale_date field?