Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Craete a calculated dimension based on variebles

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

4 Replies
swuehl
MVP
MVP

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

View solution in original post

jonathandienst
Partner
Partner

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Thank you very much all,

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

Thanks!

deepakkrish
Creator
Creator

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.