Show count of id's where payment date is greater than status date

[#1]

Hi all,

Please help me solve the below scenario ASAP as I am stuck in it.

I want count of  those ID's who have status(complete) and payment_date > Status_date.

For example, I have column ID,Status(In-queue, un-processed,processed, completed), payment_date and Status_date.

ID Status           payemt_date   Status_date

1 In-queue          05/12/2016    05/29/2016

1 un-processed   05/12/2016    05/30/2016

2 processed       06/03/2016    06/01/2016

2 completed       06/03/2016    06/01/2016

3 In-queue          05/30/2016    06/01/2016

3 complete         06/03/2016    06/03/2016

4 completed       05/12/2016    05/13/2016

5 In-queue          05/03/2016    05/03/2016

5 un-processed   05/03/2016    05/03/2016

6 completed       06/03/2016    06/01/2016

7 In-queue          05/12/2016    05/13/2016

7 un-processed   05/12/2016    05/13/2016

7 completed        06/01/2016   06/03/2016

So my output will be: Count = 2.

Pls reply urgently.

Thanks in Advance.

Ashvita.

[marjan_it]

you can write:

count(if(satatus='compelete' and payment_date > Status_date , ID))

or

count(if(satatus like 'compelete' and payment_date > Status_date , ID))

[swuehl]

Take care that your date fields are correctly interpreted, e.g. using

SET DateFormat = 'MM/DD/YYYY'; in the script before LOADing the table in.

Then, in your chart, like a text box, use

=Count({<Status = {completed}>} DISTINCT If(payemt_date >  Status_date, ID))

[#4]

Hi swuehl,

Can you portrait your logic using 'set analysis'.

Regards,

Ashvita.

[swuehl]

Sure,

have a look into

A Primer on Set Analysis

Why is it called Set Analysis?

You can look at the set expression {<Status = {completed}>} like a WHERE clause that filters your records, but faster than a per record comparison since it's using the associative logic to filter the record set.

You can also use an expression without set analysis, like Marjan suggested, which should result to the same count (leaving the DISTINCT qualifier aside) so the main difference would be a potential performance benefit from using set analysis.

You can't easily use set analysis for the date comparison, since this has to be done on a record base (you can write an expression using only set expression, but I doubt the performance will be increases).

Hope this helps,

Stefan

[#6]

Table:

Load Count(ID) as ID_Count

where match(Status, 'completed') and payemt_date > Status_date;

Load * Inline [

ID, Status,           payemt_date,   Status_date

1, In-queue,          05/12/2016,    05/29/2016

1, un-processed,   05/12/2016,    05/30/2016

2, processed,       06/03/2016,    06/01/2016

2, completed,       06/03/2016,   06/01/2016

3, In-queue,          05/30/2016,    06/01/2016

3, complete,         06/03/2016,    06/03/2016

4, completed,       05/12/2016,    05/13/2016

5, In-queue,          05/03/2016,    05/03/2016

5, un-processed,   05/03/2016,    05/03/2016

6, completed,       06/03/2016,    06/01/2016

7, In-queue,          05/12/2016,    05/13/2016

7, un-processed,   05/12/2016,    05/13/2016

7, completed,        06/01/2016,   06/03/2016

];

[#7]

Hello Swuehl ,

I was trying a different method for the underlying problem , here is my expression :

count({<Status={completed} ,payemt_date={">$(=date(status_date),'MM-DD-YYYY')"}>} distinct ID)

Can you explain why am I not getting the desired result .

[swuehl · Accepted solution]

There are some issues with this field modifier:

payemt_date={">$(=date(status_date),'MM-DD-YYYY')"}>}

- the dollar sign expansion willt return NULL unless you have limited your record set to a single possible status_date:

Use Aggregation Functions!

- You could consider something like

payemt_date={"=payemt_date > status_date"}>}

But since the field modifier is operating on field values, it will create a temporary table with payemt_date as dimension and =payemt_date > status_date as expression. Again, this expression will not return a unique value, because there are multiple combinations of the two date field values possible, hence the boolean expression returns sometimes TRUE and sometimes FALSE, no unique answer per payemt_date.

So you would need to group by ID and limit the records to status complete to get a single status_date and payemt_date per ID:

ID = {"=Only({<Status = {complete}>} payemt_date) > Only({<Status = {complete}>} status_date)"}

Since the advanced search and the set expressions do also show some overhead in calculation, and it makes the expression harder to read and maintain, I would prefer

=Count({<Status = {completed}>} DISTINCT If(payemt_date >  Status_date, ID))

But you can test both and if you see a performance gain with the set analysis only expression (which I doubt), you are free to use it.

[#9]

Thanks a lot , its always a pleasure to learn from the masters .

[pho3nix90]

This will give you the count

=Count({<Status = {'complete*'}>} If(payemt_date>Status_date, ID))