Dynamically restrict Dimension Value

[#1]

Hi All,

We have a dashboard in which user is allowed dynamically to select Month OR Week as the time dimension.

Since the underlying fact table has both week and month, there will be some records where a week falls under 2 months.

The table also has week offset and month offset identifiers.

Month Offset = 0 identifies the current month. Although we take care to load only till last completed week, there will be data for the current month. Since in most cases, the current month data will be incomplete, the requirement is to avoid the display of the current month data, when Month view is chosen.

Here are the details of the implementation so far:

[Time Dimension]:

LOAD * INLINE [

    TID, Time

    1, Week

    2, Month

];

There is this array called Time dimension. The values in the "Time" column viz, Week and Month are available in my fact table.

I also have

1) a List box based on this Time column. User can choose Week or Month using this list box. Also, this List box has been set up like a Radio Button (at least and only one column can be selected).

2) 2 sliders: One for week and another for month. The sliders overlap each other. In the Layout tab of both the sliders, I have set the "Conditional" as "=SubStringCount('|' & Concat(distinct Time, '|') & '|', '|Month|')" for Month Slider AND "=SubStringCount('|' & Concat(distinct Time, '|') & '|', '|Week|') for the Week Slider.

3) a chart that has [$(=[Time])] as the Time dimension. The chart has a few other dimensions and expressions as well. This is a line chart.

Given this setup, how can i restrict the display of the current month (month Offset =0) in both the Month Slider and in the chart, only when the Month option is selected in the List Box? Attached is a sample qvx file.

Please let me know.

Thanks.

[sunny_talwar]

Do you have a sample you can share to look at what you have thus far?

[sunny_talwar · Accepted solution]

Not sure if this is what you want, but have a look:

Expression: If(GetFieldSelections(Time) = 'MONTH', Sum({<MONTH_OFFSET = {0}>}metric1), Sum(metric1))

[#4]

Thanks Sunny. I have modified the solution a little bit to fit to our needs and the charts are now getting reflected.

But the Slider is not working properly. Still, the user was satisfied with this, as even if the slider shows the current month, if the user selects it, no data will be shown in the chart, which is fine with him.