Need to populate dates based on the Date object selected

[#1]

Hello Everyone,

We have a date object (create date) in the sheet as a filter. Based on the date selected in that object we need to populate dates like (create date - 3 days).

For example if the create date selected is 11/4/2010, we need in the chart table

Create date tickets

11/2/2010 10

11/3/2010 20

11/4/2010 30

Please let me know if there is any solution for this.

[Miguel_Angel_Baeyens · Accepted solution]

Hello,

I'd do as follows:

First, create a new empty variable, say vDateFrom and use it in a Calendar object, where minimum value equals to

=Min(CreateDate)

and maximum value

=Max(CreateDate)

Then, using something like this as expression

Sum({< CreateDate = {'>=$(=Date(vDateFrom -3))<=$(=Date(vDateFrom))'} >} Tickets)

CreateDate is formatted in the script as

Date(CreateDate) AS CreateDate

Hope that helps

[pover]

In a chart with create date as the dimension, put an expression like the following in order to see the 3 dates preceding the date selected.

sum({$<[Create date]={"<=$(=max([Create date]))>$(=max([Create date])-3)"}>} Tickets)

The 3 can be replaced with a variable and you might have to play with the syntax since max returns a number and create date is a date so sometimes it isn't a direct comparison.

Regards.

[#4]

Thanks Karl. This worked for me.Just need to add Date(max([Create date])) to your syntax

Regards,

-Prabu

[#5]

Thanks Miguel for the help. This solution also works for me.

Regards,

-Prabu