topic Variables to drive charts in QlikView

Variables to drive charts

Mon, 11 Jul 2011 12:38:59 GMT

Hello Experts,

I have a dates to variables question. I have date Dimension field called History_Date in the format of DD/MM/YYYY. I want to create 2 variables History_Date_From and History_Date_To, so that a end user makes a selection of 2 dates, then the chart should calculate the sum(sales). Earlier I have done something similar using AutoGenerate function for just weeks... but here its a full date format, so a bit confused as to how to do it.

Any suggestions are welcom and Thanks in advance

Mady

Re: Variables to drive charts

magavi_framsteg — Mon, 11 Jul 2011 13:16:53 GMT

Hi Mady.

You can use the same logic on full dates.

In QlikView dates are represented by integers.

So what you in you GUI see as 03/04/2011 is actually stored as for example 40244.

In the script you can do:

num('03/04/2011') and you will get the corresponding number.

Also, you can do date(40244) and you will get the corresponding date.

With this you can do autogenerate from minDate to maxDate the same way as you did with weeks.

Something like this:

Load Date($(vStartDate)+(Iterno()-1),'DD/MM/YYYY') as Date

Autogenerate 1

While Date($(vStartDate)+(Iterno()-1)) <=Date($(vEndDate));

Variables to drive charts

Mon, 11 Jul 2011 13:46:17 GMT

Hello Magnus,

Normally I did an rowno() as Week AutoGenerate 52; to generate 1-52 numbers for weeks to make it work previously, but I am still not able to understand how to use it here... rowno() Autogenerate ??? , but whats the max limit here???I mean I want the date till today()... is that what I need to apply here?

Also I am trying to use the History_Date as the field for a variables $(vHistoryDateFrom) and $(vHistoryDateTo) I have created and getting the dates using Calendar object, but the chart doesnt seem to work with the selections in 2 variables..and below expression?

count ({<History_Date = {'>=$(vHistory_DateFrom)<=$(vHistory_DateTo)'} >}distinct dim_opportunity.crm_opportunity_id)

Thanks Again

Mady

Re: Variables to drive charts

magavi_framsteg — Mon, 11 Jul 2011 14:14:16 GMT

Hi again Mady.

Don't forget the DOLLAR SIGN EXPANSION when using variables in {set}-analysis.

Else the variables won't be evaluated.

This should do it:

count ({<History_Date = {">=$(=vHistory_DateFrom) <= $(=vHistory_DateTo)" }>} distinct dim_opportunity.crm_opportunity_id)

Variables to drive charts

Mon, 11 Jul 2011 14:32:54 GMT

Well I am using it already....

But I was thinking, why wouldnt my variable and chart calculation work as expected?

Here is what I am doing Select Calendar Object -> Variable -> vHistoryDateFrom under min Value = min(History_Date) Max Value = Max(History_Date)

similarly I am doing another Calendar Object -> Variable -> vHistoryDateTo under min Value = min(History_Date) Max Value = Max(History_Date)

These 2 variables now show the calendar to select dates, which when selected doesnt change...and then I am using the below expression for chart calculation.

count ({<History_Date = {">=$(=vHistory_DateFrom) <= $(=vHistory_DateTo)" }>} distinct dim_opportunity.crm_opportunity_id)

Any suggestions and help, if I am doing something wrong here is welcome and appreciate it.

Thanks Again

Variables to drive charts

magavi_framsteg — Mon, 11 Jul 2011 15:05:19 GMT

Hi again Mady.

Make sure you type.

Under min value: =min(History_Date) including the equal sign.

The variable that changes according to what you select in the calendar object is:

Calendar > General > Data > Variable(s).

The min and max dates are for boundries only, but I'm sure you knew that already.

What I meant with dollar sign expansion is that in the code you pasted it said:

count ({<History_Date = {'>=$(vHistory_DateFrom)<=$(vHistory_DateTo)'} >}distinct dim_opportunity.crm_opportunity_id)

>> Look above, History_Date = {'<$(vHistory_DateFrom

You must type like this:

count ({<History_Date = {">=$(=vHistory_DateFrom) <= $(=vHistory_DateTo)" }>} distinct dim_opportunity.crm_opportunity_id)

>> You must use History_Date ={">=$(=vHistory_DateFrom)

Double equal signs.

First one is to say field History_Date should equal to bla bla bla.

And the next one after the dollar sign and paranthesis is to evaluate the variable into the set analysis.

Does this make any sense?

It's quite hard to explain without talking out loud 😃

Hope you nail it this time, im off now, but will be here again tomorrow.

Good luck!

Variables to drive charts

magavi_framsteg — Tue, 12 Jul 2011 07:21:25 GMT

Hi Mady and good morning.

Did you solve it?

Variables to drive charts

Tue, 12 Jul 2011 12:32:46 GMT

Hello Magnus,

Well I solved the problem to some extent by using the Dimension field for Varible value and it works fine now, but one issue is that, when I clear All, the dates selected in the Calendar Objects for Variable dont clear any ideas why???

Thanks Again for your time and patience,

Mady

Variables to drive charts

magavi_framsteg — Tue, 12 Jul 2011 15:02:37 GMT

Hey.

Do you mean the variable doesn't clear?

Variables have to be explicitly cleared.

So when you clear all selections, make sure to also reset the variable to your preferred default date.

Variables to drive charts

Tue, 12 Jul 2011 15:20:40 GMT

Yes Magnus,

I want to clear off the variables when I select clear ALL and get blank values(not default dates) where I can select the dates again...Is this possible

Variables have to be explicitly cleared...??? Can you explain more about this please?

Thanks in advance,

Mady

Variables to drive charts

magavi_framsteg — Wed, 13 Jul 2011 08:02:52 GMT

Hi again.

Usually when I want to do this, I create a button with a series of actions:

- 1. Clear all or clear certain fields

- 2. set variable vFromDate = (blank)

- 3. set variable vToDate = (blank)

- 4. some other action