Variable set up issue

[Anonymous]

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!

[Oleg_Troyansky]

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.

[Anonymous]

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.....

[Anonymous]

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.....

[Anonymous]

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.....

[Anonymous]

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

[#7]

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

[Anonymous]

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.