Input Box and Variables Issue

[Anonymous]

Hi all,

I'm trying to use an input box in order to make a selection between a month range, currently I have the following Input Box:

And the following variables:

But I don't know how to link the variables to the Month dimmension, how do I do that?

And, after that, how do I use the variables in the expression? Is it something like this?

Sum( {<Month = {'>=$(=Month(vComparisonStart)) <=$(=Month(vComparisonEnd))"}>} Sales)

Thanks!

[sunny_talwar]

How is Month field created in the script?

[Anonymous]

I need to break the date down because there are different formats from the tables I get it from. This is the base one:

     Month(Date(Date#(DATE,'YYYYMMDD'),'DD/MM/YYYY')) as Month,
     Year(Date(Date#(DATE,'YYYYMMDD'),'DD/MM/YYYY')) as Year,
     Day(Date(Date#(DATE,'YYYYMMDD'),'DD/MM/YYYY')) as Day,

[sonkumamon]

Hi Marcelo,

If I were you, I would create additional 4 fields in my master calendar and define the as an input fields.

Then I would set each variable to the relevant field.

such as:

Inputfield ComparisonStart;

Inputfield ComparisonEnd;

Inputfield CurrentStart;

Inputfield CurrentEnd;

LOAD MasterCalendar:

(Date(Date#(ComparisonStart,'YYYYMMDD'),'DD/MM/YYYY')) AS ComparisonStart,

(Date(Date#(ComparisonEnd,'YYYYMMDD'),'DD/MM/YYYY')) AS ComparisonEnd,

(Date(Date#(CurrentStart,'YYYYMMDD'),'DD/MM/YYYY')) AS CurrentStart,

(Date(Date#(CurrenEnd,'YYYYMMDD'),'DD/MM/YYYY')) AS CurrenEnd,

........

;

SET vComparisonStart = ComparisonStart;

SET vComparisonEnd = ComparisonEnd;

SET vCurrentStart = CurrentStart;

SET vCurrentEnd = CurrenEnd;

[#5]

Hi,

When we use input box with inputs for variables, we can set the values of those variables.

Those values given in the input box will affect charts when the variables are used in the chart.

For example in your case if you input value for comparison start and comparison end and use the variables vComparisonStart and VComparisonEnd in a chart, the input values will affect that chart.

[Anonymous]

I don't have a MasterCalendar, how does that work?

Do I add these lines in my tables that have these dates?

[sonkumamon]

Hi Marcelo,

Master Calendar is a table which contains all the dates you set it to contain (for example, the orders start date as the start date and 3 years from now as the end date).

See the attached file, just change the Min and Max date according to your data.

If you don't have a master calendar (although it's recommended in every model), just add this lines to the table which contains the dates fields, as you wrote.

Uri