Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

jean-luke
New Contributor

Date in set analysis

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

1 Solution

Accepted Solutions
borisman
Contributor III

Re: Date in set analysis

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.

5 Replies
borisman
Contributor III

Re: Date in set analysis

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.

Re: Date in set analysis

Hi,

You can use if()

Try like

Count(if(signup_date=usage_date),customer)

Regards,

Regards,
Prashant Sangle
borisman
Contributor III

Re: Date in set analysis

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

MVP
MVP

Re: Date in set analysis

1.jpg

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

in textbox

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

Re: Date in set analysis

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

Community Browser