topic Re: Date in set analysis in QlikView

Date in set analysis

Anonymous — Sun, 15 Mar 2015 14:34:19 GMT

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 — Sun, 15 Mar 2015 15:23:27 GMT

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 — Sun, 15 Mar 2015 15:29:04 GMT

Hi,

You can use if()

Try like

Count(if(signup_date=usage_date),customer)

Regards,

Anonymous — Sun, 15 Mar 2015 15:31:35 GMT

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

maxgro — Sun, 15 Mar 2015 16:15:16 GMT

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

in textbox

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

MK_QSL — Sun, 15 Mar 2015 21:29:54 GMT

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