topic Re: How to use dates interval as dimension ? in QlikView

How to use dates interval as dimension ?

yanivvl0 — Tue, 08 Mar 2016 10:32:26 GMT

hi to all ,

i have Projects table with start_date field for each project . for each project there are many Registrations in Registrations Table with start_date field for each Registration. i need to count the number of Registrations ( for each Project ) accordind to the interval days btween Project start_date and Registrations start_date .

for example:

interval of 1 day - project 10 have 100 Registrations , roject 20 have 150 Registrations .

interval of 2 day - project 10 have 60 Registrations , project 15 have 200 Registrations , roject 20 have 30 Registrations .

...

i tried using aggr in calculated dimension but there is a problem when compute the interval days ,

thanks.

Re: How to use dates interval as dimension ?

tresB — Tue, 08 Mar 2016 10:57:13 GMT

I guess you are on right track. Could you post a sample and explain the issue?

Re: How to use dates interval as dimension ?

yanivvl0 — Tue, 08 Mar 2016 12:54:59 GMT

hi tresesco , here is demo data , thanks.

Re: How to use dates interval as dimension ?

Tue, 08 Mar 2016 16:50:07 GMT

hi yaniv:

Use a chart object (instead of a table box) and use the calculated dimension:

=floor(ProjectDate-RegistrationDate)

Use an expression of

=count(RegistrationID)

This will yield registrations by interval before project per your sample data. Sample QVW attached.

Re: How to use dates interval as dimension ?

tresB — Wed, 09 Mar 2016 04:49:20 GMT

And how would be your expected output?

Re: How to use dates interval as dimension ?

PradeepReddy — Wed, 09 Mar 2016 05:15:14 GMT

might be like this...

Dimension: Class(ProjectDate-RegistrationDate,5)

Expression : count(distinct RegistrationId)

If this is not what you are expecting, post the expected output...

Re: How to use dates interval as dimension ?

yanivvl0 — Wed, 09 Mar 2016 05:26:51 GMT

like that :

Re: How to use dates interval as dimension ?

tresB — Wed, 09 Mar 2016 05:58:17 GMT

May be like attached.

Re: How to use dates interval as dimension ?

yanivvl0 — Wed, 09 Mar 2016 06:14:35 GMT

hi Greg , so simple and so smart - thanks for the great answer !!

Re: How to use dates interval as dimension ?

yanivvl0 — Wed, 09 Mar 2016 06:21:33 GMT

Hi Tresesco , Great answer, thanks !!

Re: How to use dates interval as dimension ?

yanivvl0 — Wed, 09 Mar 2016 06:27:00 GMT

hi pradeept , yes , it is a very good solution , THANKS !!