topic Re: Problem with set analysis in QlikView

Problem with set analysis

ttal7878 — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Problem with set analysis

PrashantSangle — Thu, 28 Sep 2017 09:46:13 GMT

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

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

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

Unique)

Write = before variable Name in set anlaysis

Regards,

Re: Problem with set analysis

ttal7878 — Thu, 28 Sep 2017 11:12:52 GMT

It does not work

Re: Problem with set analysis

sunny_talwar — Thu, 28 Sep 2017 11:17:43 GMT

Do you get a result without the set analysis on date

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

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

Unique)

Also, do you get a result with just the set analysis on date?

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

Just trying to figure out if the issue is in the date part of the expression or something else

Re: Problem with set analysis

PrashantSangle — Thu, 28 Sep 2017 11:19:29 GMT

Hi,

debug your expression,

take straight table

use sale_date as dimension

in expression write above expression.

then move your arrow on straight table header and check your expression is evaluating what value.

Regards,

Re: Problem with set analysis

PrashantSangle — Thu, 28 Sep 2017 11:20:22 GMT

Check date format also is it matching with Sale_Date??

Regards,

Re: Problem with set analysis

ttal7878 — Thu, 28 Sep 2017 11:25:16 GMT

yes,I'm getting the result without the set analysis on date.

and

I'm not getting the result with just the set analysis on date

Re: Problem with set analysis

ttal7878 — Thu, 28 Sep 2017 11:26:39 GMT

The date is in the same format, you can see in the picture I have attached

Re: Problem with set analysis

ttal7878 — Thu, 28 Sep 2017 11:33:22 GMT

That's what I see

Re: Problem with set analysis

sunny_talwar — Thu, 28 Sep 2017 11:40:36 GMT

Start and end date are the same exact timestamps... do you have something that precise in your data?

Re: Problem with set analysis

ttal7878 — Thu, 28 Sep 2017 11:48:32 GMT

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

Re: Problem with set analysis

sunny_talwar — Thu, 28 Sep 2017 11:52:40 GMT

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')

Re: Problem with set analysis

ttal7878 — Thu, 28 Sep 2017 12:02:56 GMT

still not working,

Re: Problem with set analysis

sunny_talwar — Thu, 28 Sep 2017 12:04:28 GMT

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

Re: Problem with set analysis

ttal7878 — Thu, 28 Sep 2017 12:09:24 GMT

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

Re: Problem with set analysis

sunny_talwar — Thu, 28 Sep 2017 12:22:21 GMT

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

Re: Problem with set analysis

ttal7878 — Thu, 28 Sep 2017 13:13:51 GMT

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

Re: Problem with set analysis

sunny_talwar — Thu, 28 Sep 2017 13:31:52 GMT

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

Re: Problem with set analysis

sudeepkm — Thu, 28 Sep 2017 19:22:47 GMT

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.

Re: Problem with set analysis

lorenzoconforti — Thu, 28 Sep 2017 21:55:55 GMT

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