cancel
Showing results for 
Search instead for 
Did you mean: 

Aggregation in Dashboard

Former Member
0 Kudos

Hi

I have a dashboard in Xcelsius. And i have filters in it using combo boxes. I have implemented All functionality in the filters, like when the user selects ALL in filters, all data is selected for that filter.

Data in the chart changes, as per the selections done in the filters.

i have the data like

Sales PersonMonthDepartmentSales
AllAllAllAll
SteveJan-12A200
SteveJan-11A230
SteveFeb-12B140
SteveFeb-11B150

I have Sales Person,Month as filters. And chart which is having Sales based on Department.Now when i select ALL, in Month filter, all months are selected.And  I get 2 bars for Department A and 2 For  Department B.

But i want, if All is selected for Month Filter.Data should be aggregated for Department A and B. And i should get only 1 bars for Department A and 1 for B.

I am trying this using Sumif. Can you elaborate more, usage of this method.And i think it will impact performance. So is there some better method than using sum if.

Thanks in advance..

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi,

in case your source comes from BEX, you can put sales person and month in free charachteristics, and then, refresh the query according to the chosen value.

in this case, the data would be aggregated in the bex level and you wont have to perform the aggregation in excel (which is usually slower in performance aspect)

hope it helps,

Roman

former_member184839
Active Contributor
0 Kudos

Hi Ronny,

Sumif function ll be the best to do this.

        A                                         B               C                        D

1.  Sales Person             Month   Department      Sales
2.   All               All   All          All
3.  SteveJan-12    A200
4.  SteveJan-11    A230
5.  Steve    Feb-12    B140
6.  SteveFeb-11    B150

All you gotta do is.

1. Take the destination row, Suppose (A7:D7).

2. In another row, suppose , A8:D8 ,  write the formula in the cell A8= if(A7="","" ,A7)

Drag this over till D8.

3. In the cell, D8=IF(B7="All",SUMIF(C2:C5,"A",D2:D5)+SUMIF(C2:C5,"B",D2:D5),D7)

4. Link these cells to your chart.

I hope that helps.

Thanks,

Sara

Former Member
0 Kudos

Hi Sara,

Thanks for your reply.

In the above solution.The sales is getting summed up for Department A and B both in one cell,when ALL is selected in Month Filter.But what i want is Department A and B should be summed up separately

And there should be one bar for Department A(Both rows for Dept. A Summed Up) ans One bar for department B(Both Rows for Dept. B summed up).

Please let me know in case you need more clarity on the requirement.

Thanks a lot for your help.

former_member184839
Active Contributor
0 Kudos

Hi Ronny,

Take the same example.

Now follow the same steps. 

1. Take the destination row, Suppose (A7:D7).

2. In another row, suppose , A8:D8 ,  write the formula in the cell A8= if(A7="","" ,A7)

Drag this over till D8.

3. In the cell, D8=IF(B7="All",SUMIF(C2:C5,"A",D2:D5),D7)

4. In the cell, E8=IF(B7="All",SUMIF(C2:C5,"B",D2:D5),"")

5. Link these cells to your chart (your y axis values ll have both d8 and e8 cells) .Don't forget to check "Ignore the Blank cells" at the end of range and series.

I hope that helps.

Thanks,

Sara

Former Member
0 Kudos

Hi Sara,

i am facing one problem in this scenario. As the data i want to show is coming dynamically in the embedded excel based on the filter selection, i have made.

So for Sumif function i cannot hard code "criteria" parameter.

From the list of departments, i am getting. I need to find the unique deparments. So that i can use them in the Sumif function and can aggregate my data based on the matching deparments.

Can you provide any solution for this.

Thanks in advance....

Former Member
0 Kudos

Hi Ronny,

In order to get the unique category names, do the following:

1. Find all unique category names.  To do this, add the following to column E:

E3 = 1

E4 = IF(COUNTIF($C$3:C3,C4)=0,MAX($E$3:E3)+1,-1)

And copy this down.

So E3 = 1 is the first unique category, E3 = 2 is the second unique category, etc.

Now in Sara's formula

IF(B7="All",SUMIF(C2:C5,"A",D2:D5),D7)

replace the department name 'A' with:

INDEX(C3:C6,MATCH(1,E3:E6,0))

and 'B' with

INDEX(C3:C6,MATCH(2,E3:E6,0))

and if you have a third category, it would be

INDEX(C3:C6,MATCH(3,E3:E6,0))