Writing a set analysis issue

[#1]

I only want to count the test_id's when the interventiondate is later in time then the datetime.

The expression below I used doesn't work:

count(distinct {<interventiondate={">datetime"} >} test_id)

How do I write this?

Kind regards,

Katleen

[manideep78]

If you are fine with date format use this:  count({<interventiondate={">datetime"} >} distinct test_id)

else convert the datetime to the format you use and try this:

count({<interventiondate={">$(=Date(datetime))"} >} distinct test_id)

[#3]

Apparently I also need to count when the dates are the same.

I tried to adapt it like this, but it doesn't work:

count({<interventiondate={">=$(=Date(datetime))"} >} distinct test_id)

What did I do wrong this time?

[er_mohit]

Make sure date format of both is same

[#5]

interventiondate is a datefield.

datetime is a timestamp, but that should be solved with the date(datetime) function, no?

[simenkg]

use count({<interventiondate={">=$(=Date#(datetime,'DD.MM.YY hh:mm:ss'))"} >} distinct test_id)

replace 'DD.MM.YY hh:mm:ss' with the format you use for the timestamp.

[simenkg]

However if you want this to work as FOR each line: if interventiondate >= datetime count(distinct test_id) .

Then you have to use

sum(aggr(count({<interventiondate={">=$(=Date#(datetime,'DD.MM.YY hh:mm:ss'))"} >} distinct test_id),test_id))

[#8]

Still doesn't seem to work, I have attached a qvw witch some sample data.

Can you take a look at it?

Kind regards,

Katleen

[er_mohit]

See the attached file

[simenkg]

In your load script change it into the following:

Actions:

load *,

  if(interventiondate>=datetime,1,0) as IntDateBiggerThanDateTimeFlag;

LOAD _id as id,

     action_category,

     actionname,

     status,

     interventiondate,

     process_datetime as datetime

FROM

[Apps\2. Extraction to QVD layer\QVD_Ettenleur\Actions_69.qvd]

(qvd);

Then your expression becomes:
count({$<IntDateBiggerThanDateTimeFlag={1}>} distinct id)