Craete a calculated dimension based on variebles

[#1]

Hi,

I need to create a chart whose dimension will be created according to 2 variables: one indicates the start date, and the other indicates the end date.

For example, if vStartDate is 2012-01-01 and vEndDate is 2012-01-05, then the dimension of the chart will be consist of all dates between these dates:

2012-01-01, 2012-01-02, 2012-01-03,2012-01-04, 2012-01-05

Thanks,

Guy

[swuehl · Accepted solution]

Do you want to limit values of an existing date field to a certain range?

Then you can probably use a calculated dimension like

=if( DateField >= '$(vStartDate)' and DateField <= '$(vEndDate)', DateField)

You can also use just DateField as dimension and add a set expression to all your expressions (that need to use an aggregation function then), like:

=sum( {< DateField = {">='$(vStartDate)'<='$(vEndDate)'"} >} Value)

There are often some issues with comparing a variable containing a textual representation of a Date with a date field, i.e. with non-matching date formats. It might be a good idea to create a numerical for your date variables and use these in your calculated dimension / expressions.

Hope this helps,

Stefan

[jonathandienst]

Guy

Unless there is more to your needs than I understood, perhaps a better way to achieve what you are after is to use date as the dimension, and put a set expression filter in your expression, something like:

     Sum({<Date = {">=$(=Date(vStartDate)) <=$(=Date(vEndDate))"}>} Sales)

The chart will limit the display to the date range you need and it will perform far better as the filtering is done before the chart is created.

Hope that helps

Jonathan

[#4]

Thank you very much all,

Indeed using Set Expression is better and more elegant way of solving this problem.

Thanks!

[deepakkrish]

Hi ,

I am using this expression in my chart

=if( DateField >= '$(vStartDate)' and DateField <= '$(vEndDate)', DateField)

The problem is the timestamp field is locked . 

I have to show last 5 weeks trend from this selected timestamp. But still i could see only the selected timestamp value. if i unlock the field i can see all five timestamp value. how to show trends dynamically  using calculated dimension.  can you give me the answer for this

Regards,

Deep.