topic Display years dynamically in QlikView

Display years dynamically

Tue, 28 Jun 2011 13:19:10 GMT

Hi Kaushik,

In my file i need to display last years in a single to variable based on one field, it should be dynamical changes.

In below to statement is a field "DistinctCaptureDate", based on this field last 7 years should display.

Year(max(DistinctCaptureDate))-1 to Year(max(DistinctCaptureDate))-7 should store in a single variable.

How can i work on this.

Pls check the attached QVW file.

Regards

Venkat

Re: Display years dynamically

Miguel_Angel_Baeyens — Tue, 28 Jun 2011 14:18:01 GMT

Hello Venkat,

Don't know if this makes many sense, but you can try with

=Concat(ValueLoop(Max(TOTAL Year(DistinctCaptureDate)) -7, Max(TOTAL Year(DistinctCaptureDate)) -1, 1), ',')

To get all the values within the range.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Display years dynamically

Tue, 28 Jun 2011 14:27:38 GMT

Hi Miguel,

Thanks for your reply.

I tried your code in variable value. Then it is showing like this 2004,2005,2006,2007,2008,2009,2010.

But it should show like below (in a vertical order without having commas).

2004

2005

2006

2007

2008

2009

2010.

Regards

venkat

Re: Display years dynamically

Tue, 28 Jun 2011 14:30:58 GMT

Concat(ValueLoop(Max(TOTAL Year(DistinctCaptureDate)) -7, Max(TOTAL Year(DistinctCaptureDate)) -1, 1), chr(13))

Re: Display years dynamically

swuehl — Tue, 28 Jun 2011 14:32:46 GMT

Hi,

see attached sample, mostly the already proposed way.

Regards,

Stefan

Re: Display years dynamically

Tue, 28 Jun 2011 14:44:55 GMT

Hi swuehl,

I cannot open your file, bcoz my Qlikview is personalized version.

Pls send the steps to implement in my QVW file.

Regards

venkat

Re: Display years dynamically

swuehl — Tue, 28 Jun 2011 14:48:13 GMT

Hi venkat,

set Variable vNYear to

ValueLoop(year(vMaxCapDate)-7,year(vMaxCapDate)-1, 1)

(no equal sign)

Set field expression to

=$(vNYear)

Regards,

Stefan

Re: Display years dynamically

Tue, 28 Jun 2011 14:54:38 GMT

Hi Swuehl,

Thanks for reply.

It is showing years in listbox but one problem.

I tried your steps BUT when i select a particular year in listbox, it is not selecting.

Why ?

Regards

Venkat

Re: Display years dynamically

swuehl — Tue, 28 Jun 2011 15:04:59 GMT

Hi,

well, we have just created a list of values, no connection to other data tables. So you can't use this box for selection, just for display.

As I understand, the task is to generate just the list of the years, but there is no requirement for selection.

If there is a requirement, you might need to add a calendar to your data model (and then limit the list box to the given years, but this is somewhat more then just two lines).

[Besides, there is no data for past years ]

Regards,

Stefan

Re: Display years dynamically

Wed, 29 Jun 2011 05:04:57 GMT

Hi Swuehl,

In the attached QVW file, there is a field "DistinctCaptureDate". Based on that last 7 years should display in the listbox.

When i select a particular date from "DistinctCaptureDate" field that year should be highleted in the "DistinctCaptureyear" listbox.

How can i work on this. Pls see the attached QVW file.

Regards

Venkat

Re: Display years dynamically

swuehl — Wed, 29 Jun 2011 09:53:53 GMT

Hi venkat,

as I said in my previous post, there is no connection between this list of 7 years and your DistinctCaptureDate.

We created a synthetic dimension, but no link to your data model.

If you want to filter your data for years, you can add a year field to your data model and filter this for say the last seven years (if you want to display all this seven years without having actual data, you can additionally add a calendar table to your data model). I posted a proposal for this to another of your postings yesterday.

It's pretty hard to tell what is the right solution for your needs, maybe you can tell us the business need or learning exercise detail description 😉

Regards,

Stefan