topic Craete a calculated dimension based on variebles in QlikView

Craete a calculated dimension based on variebles

Sun, 20 May 2012 08:47:25 GMT

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

Craete a calculated dimension based on variebles

swuehl — Sun, 20 May 2012 12:01:02 GMT

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

Craete a calculated dimension based on variebles

jonathandienst — Sun, 20 May 2012 12:04:35 GMT

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

Craete a calculated dimension based on variebles

Sun, 20 May 2012 14:07:27 GMT

Thank you very much all,

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

Thanks!

Re: Craete a calculated dimension based on variebles

deepakkrish — Wed, 16 Sep 2020 12:29:28 GMT

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.