Count of a field based on two dates

[#1]

Hi All,

I am using a chart to display the count of some records. I have a condition where I need to display the sum of records if one date = another.

Consider this example.

Name	Date1	Date2
A	4/10/2015	4/10/2015
B	4/10/2015	4/10/2015
C	4/10/2015	4/11/2015
D	4/11/2015	4/12/2015
E	4/11/2015	4/12/2015

I need the output as

Date	Count
4/10/2015	2
4/11/2015	0
4/12/2015	0

Count of 2 includes records A and B because Date1=Date2. And 0 for the other records.

Could you please advise how this can be done?

Thanks in advance.

[#2]

Hi Varun,

I would do this comparison in the script to create a flag, which you can then easily use within set analysis.

Load

Name,

Date1,

Date2,

If(Date1=Date2,1) As MatchedDateFlag

From.....

Then in your expression simply

Count( {<MatchedDateFlag={1}>} Date1)

hope that helps

Joe

[sushil353]

Hi,

Please try below code:

Test:
LOAD *,if(Date1=Date2,'1','0') as flag Inline
[
Name,Date1,Date2
A,4/10/2015,4/10/2015
B,4/10/2015,4/10/2015
C,4/10/2015,4/11/2015
D,4/11/2015,4/12/2015
E,4/11/2015,4/12/2015
];

Table2:
LOAD sum(flag) as count,
Date2 as Date
Resident Test
Group by Date2;

HTH

Sushil

[Anonymous]

Hi,

PFA

Thanks

BKC

[jagan · Accepted solution]

Hi,

If both the fields are in the same table then you can do it in script easily like below

Data:

Load

Name,

Date1,

Date2,

If(Date1=Date2,1, 0) As DateFlag

From DataSource;

Now in front end you can use

Dimension: Date1

Expression: Sum(DateFlag)

If both the fields are in different tables then try like below

Dimension: Date1

Expression: Sum(Aggr(If(Date1= Date2, 1, 0), Date1, Date2))

Hope this helps you.

Regards,

Jagan.

[#6]

Hi Joe,

The two fields are from different tables and hence I did not use this method. I used Jagan's method and the results were accurate.

Thanks for your quick response. Really appreciate it.

[#7]

Hi Jagan,

This method worked fine. The fields were indeed from two different tables.

Thanks for the quick response.