topic Re: Variable set up issue in QlikView

Variable set up issue

Anonymous — Wed, 10 Jul 2013 21:40:26 GMT

Hi,

I have a fact table where it contains transactional details for a rolling two years, and it has a date field called EFFECTIVE_DATE.

I have another dimension table named MasterCalendar, where it has consecutive dates from 1/1/2010 to 12/31/2020 and the corresponding YEAR, MONTH, etc.

The two tables are linked on EFFECTIVE_DATE (field named the same, but no "join" statement used).

I set up a variable using Variable Overview as follow: vMaxDate=max(EFFECTIVE_DATE)

I build up a multi box with Fields YEAR and MONTH (from the dimension table), and I add a inputbox to show the value of my variable.

My thought is if I select YEAR = 2013 and MONTH= Jan, Feb, Mar as my current selection, my variable vMaxDate should = 3/31/2013.

While in fact, sometimes the variable returns 3/31/2013, and sometimes 12/31/2020.

The current selections are always the same, so I don't understand why the variable returns different values, and it seems a random event to me.

Has anyone ever had the same issue as mine?

Thanks for your help!

Re: Variable set up issue

Oleg_Troyansky — Thu, 11 Jul 2013 02:48:47 GMT

Most likely, either the variable isn't getting refreshed when the selection is made, or the Input Box is not being refreshed. Input boxes have all sorts of glitches like that - for example, you can enter a new value and it won't get remembered until you press Enter.

Try displaying the value in a text box and see if you are getting more consistent results.

Re: Variable set up issue

Anonymous — Thu, 11 Jul 2013 13:04:51 GMT

Oleg,

Thanks for your reply. I already tried displaying the value in a straigt table and in a label for an expression, but neither of those worked consistently. Sometimes, the input boxes and the straight table even displayed different values.....

Re: Variable set up issue

Anonymous — Thu, 11 Jul 2013 13:05:00 GMT

Oleg,

Re: Variable set up issue

Anonymous — Thu, 11 Jul 2013 16:22:59 GMT

Oleg,

Re: Variable set up issue

Anonymous — Thu, 11 Jul 2013 17:15:30 GMT

Make sure that the EFFECTIVE_DATE has the same format in both tables, and it is always the integer date, no fractions (hours, minutes). To do this, use this in the Fact table:

date(floor(EFFECTIVE_DATE)) as EFFECTIVE_DATE

Re: Variable set up issue

Thu, 11 Jul 2013 17:21:43 GMT

One small suggestion is do not load Date Dimension past the dates you have in the fact table unless you need it... use a sub query to load Date Dimension

Re: Variable set up issue

Anonymous — Thu, 11 Jul 2013 17:27:20 GMT

Somewhat agree 🙂

The rules for the calendar may be defind by fixed date, as in this case, or by the dates in the data. I use sometimes a mix, for example start date is the start of the year of the eraliest transction date, and the end date is the end of the year of the latest transaction date.