6 Replies Latest reply: Apr 13, 2015 7:19 AM by varun raj

# Count of a field based on two dates

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.

• ###### Re: Count of a field based on two dates

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

• ###### Re: Count of a field based on two dates

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.

• ###### Re: Count of a field based on two dates

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

• ###### Re: Count of a field based on two dates

Hi,

PFA

Thanks

BKC

• ###### Re: Count of a field based on two dates

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.

• ###### Re: Count of a field based on two dates

Hi Jagan,

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

Thanks for the quick response.