I got a mail this morning about a blogpost I wrote 2,5 years ago in which I explained how you can use the Push Button component in SAP BusinessObjects Dashboards to increase or decrease a value. The question in the mail was if and how this example could be transformed into a month selector, where the minus button would set February back to January and the plus button would do this the other way around.
There are two challenges here: How to get the names of the months to be shown (and not just the numbers) and how to limit the model to only work between the lowest (January) and highest (December) possible value.
To solve this I used a Source Data component. This is a ‘hidden’ component that can move data based on a index number. To set this up I put all the months in the spreadsheet and for clarity I put the corresponding index numbers in front of them. Note: The Source Data component starts with index 0!
As you can see in the two screenshots below I selected the cells with the months [B5:B16] as my source data from which one cell value has to be inserted into destination [D2]. Item Index [C2] determines which value that should be. In the screenshots the index number is 0, so “Jan” should be displayed in [D2].
The Push Button components are set up to change the index number by inserting the value from [A2] (minus) and [B2] (plus) into [C2].
These source data cells for the two Push Button components use an If/Then formula to ensure that a index number lower than 0 [A2] or higher than 11 [B2] isn’t possible:
[A2] =IF(C2=0,0,C2-1) – If index number is 0 (January), keep the value at 0, otherwise decrease it with 1.
[B2] =IF(C2=11,11,C2+1) – If index number is 11 (December), keep the value at 11, otherwise increase it with 1.
Check the model for yourself. The display cell [D2] is bound to an Input component by the way. You can download the source file here (via Pay with a Tweet/Facebook).