I have used a filter component for the user to choose Region and Subregion. The requirement is to have one more entry, with value ‘All’ from both Region and Subregion which will show the aggregated $ amount.
Should the source data be modified to include this entry or is there a way out to do this with Xcelsius tool itself?
Your thoughts pls
More to the point, it should be done in Excel, to get the most out of Xcelsius you need to get your head around manipulating data in the underlying Excel model.
Hi, you can use something like a concatenation between your filters and in a new column you can make a formula in which you put 1 if the concatenation is equal to your filters and 0 else. use this column in a combobox and that’s it. be ensure that the default value of your combo box will be 1.
This can easily be accomplished using a simple crosstab in excel, please see the URL below showing how to hand build the crosstab. Once the crosstab is built then you can easily get your sums accross your regions/subregions. I included your example in a xlf I did, please see the attached. Hope this helps you out.
I too am trying to add the ALL level to the filter…find myself stuck
Raw data I have is something like this
Col1 Col2 Col3
Crew A 10
Crew B 20
Crew C 30
Non Crew D 40
Non Crew E 50
ALL
I need to feed Col1 as label vaues to a combo box. add ‘ALL’ to that list.
The combo box is feeding a List builder. Hence when the user selects ALL from the combo box the List builder source is populated with A, B, C, D, E.
Use the filter with “Filtered Rows” condition and use the labels as the source.
Then send the result on a single cell (I call colX for this example).
In the X column you need to set a formula like =if(or($col$X=“val”,$col$X=“All”),“X”,"").
You’ll have a column with X and BLANKS.
Now you can create your filter using a combobox in which you’ll copy your entire table, including the X column in a result table.
NB: you need to set as X the header and assign this value as a defalut for your Xcombobox.
The All level is done!!
If I was not clear just let me know, I’ll create a sample on Xcelsius (v. 2008 SP1).
i have a couple of questions based on this example.
Rows are not filtered in the combo box based on the value in the combo box.
Eg: this is my data
LOB PF FL
ALL ALL ALL
CG BE CA
CG BE CA
CG BE GG
CG DR BCD
CG DR BCD
CG DR BCD
CG DR BCD
RC FS CA
RC FS CA
RC FS CA
NI FR CA
NI FR CA
NI FR CA
Based on the value selected ‘ALL’ in the combo box , you will get all the rows, but When I select CG then PF Combo box should show only BE AND DR, but PF combo box shows all the VALUES BE, DR,FS,FR. How can I acheive like this.
LOB PF FL
CG BE CA
CG BE CA
CG BE GG
CG DR BCD
CG DR BCD
CG DR BCD
CG DR BCD
Hi,
so you need a cascading filter… here a quick sample I have done for a training at a customer. please notice that I was only using two filters but it is easy to change from 2 to 3 filters.
Hi,
of course I can send you the solution, but don’t you think you did not try enough to solve it? I sent a sample that uses 3 filters instead of 2. it means you only need to replicate the same logic to the filter 3 and modify a bit the IF formula that manages its labels.
Can you explain me what you cannot understand to complete this? It is better if I help you with words instead of providing you a complete solution that makes you think less.
So can you tell me where you have the error? can you provide any file to check?
(BOB member since 2007-10-09)
(BOB member since 2008-08-27)