How to use dates interval as dimension ?

[yanivvl0]

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.

[tresesco]

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

[yanivvl0]

hi tresesco , here is demo data , thanks.

[#4]

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.

[tresesco]

And how would be your expected output?

[PradeepReddy · Accepted solution]

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...

[yanivvl0]

like that :

[tresesco]

May be like attached.

[yanivvl0]

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

[yanivvl0]

Hi Tresesco , Great answer, thanks !!