Freeze one value within a dimension

[#1]

Hi there!

Hoping someone out there can offer me some assistance.

I have a horizontal bar chart with one dimension (Scenario) where I want to hold one value constant but allow the others to vary.

In the illustration below, I have three scenarios, actual budget and forecast. How do I freeze the budget so that when I vary the months, only the actual and forecast numbers/bars change. The set analysis for the chart below is as follows:

sum ({<[SCENARIO]=,[YEAR]=,[CalendarDate]=>} Quantity)

thanks!!

[erichshiino]

Hi, Mike

Try this:

sum ({<[SCENARIO]+={Actual},[YEAR]=,[CalendarDate]=>} Quantity)

If you want to lock Actual on your chart. The others will depend on selections

[#3]

Hi Erich,

thanks but this is not correct. What you are suggesting is only freezing the 'Actual' bar so that when I make a selection, Actual is always on the chart. What I want to do is enable the chart to always set the Budget = 114,975,625 regardless of what calendardate selection I make. Actual and forecast needs to change based on the CalendarDate selection but not the Budget.

Any additional insights you can offer would be greatly appreciated

[Anonymous]

Hi Mike,

You can try to use two expressions. One for the Budget only, and one for other scenarios (excl. Budget):

expr1: sum ({<[SCENARIO]={Budget},[YEAR]=,[CalendarDate]=>} Quantity)

expr2: sum ({<[SCENARIO]-={Budget},[YEAR]=,[CalendarDate]=>} Quantity)

Keep in mind that expr1 locks the budget only for selections in the Year and CalendarDate fields. So, if there are other possible selectors (i.e. Month or Product), the budget value will be dynamic and reflect the selections in those fields. If you want to make it absolutely static, you can use:

expr1: sum ({1<[SCENARIO]={Budget}>} Quantity)

or

expr1: sum ({1<[SCENARIO]={Budget}, [YEAR]={'2011'}>} Quantity)

or

expr1: sum ({1<[SCENARIO]={Budget}, [YEAR]={'$(year(today()))'}>} Quantity)

Let me know if it works, the way you need it to, please.

Dilyana

Consultant

CapricornVentis