Date in set analysis

[Anonymous]

Hi all.

I have 2 different date fields, signup date and usage date. I want to make a count where these 2 date fields are the same ex.

Customer              Signup date     Usage date         

       A                   01/01/2012       03/01/2012

       B                   02/01/2012       02/01/2012

Thus count(customer) should only give me 1

Please assist.

Thank you

J/L

[Anonymous · Accepted solution]

best way is to flag them in the load script so you would create another field in your load statement:

if([Signup date]=[Usage date],1,0) as [Is Customer Dates Same]

and then you can count them in your charts below granted your dimension will be Customer:

SUM([Is Customer Dates Same])

Always try to do any calculations you can in your load script - this will improve performance and simplify expressions.

[PrashantSangle]

Hi,

You can use if()

Try like

Count(if(signup_date=usage_date),customer)

Regards,

[Anonymous]

while this would work, performance wise nested IFs inside aggregation functions is not a good idea normally for larger data sets

[maxgro]

=fabs(sum([Signup date]=[Usage date]))

in textbox

=sum(aggr(fabs(sum([Signup date]=[Usage date])), Customer))

[MK_QSL]

=Count({<[Signup date] = {"=[Signup date] = [Usage date]"}>}DISTINCT Customer)