Component Solution Services REC02F Manual de usuario descargar pdf (Pagina 31)

Tips Recipes Brio 2002

Section Type: Table section

Tip Name: Qualified Sub-Totals #2

Display all contributors that account for the top 40% of revenue within

each group.

Purpose / Use: You don’t want a fixed number of contributors (which would be a TopN

analysis). Instead, you want all the contributors (however many there

are) that account for the top 40% of your total revenue for each group.

Sample File: Prod fam within store type up to 40%.bqy

Steps to Produce: This solution requires two Table sections. One table section to create the

needed summary calculation, then another to convert the summary field

to a regular number upon which a limit can be applied.

1) Build your query as normal and process it to fetch some initial data.

2) With the Results section selected, add a new Computed Item named “Pcnt of Store Type”

using the following formula:

Amount_Sales/Sum(Amount_Sales,Store_Type)

3) Now, insert a new Table section and drag all fields into the Table section design.

4) With the Table section selected, sort the Table on the category field used for the grouping

and then on the percent calculation field. In this example, you would sort on “Store Type”

then on “Pcnt of Store Type”.

5) Add a new Computed Item to the Table section named “Top 40% within Store Type” with

the following formula:

Cume(Pcnt_of_Store_Type,Store_Type)

This calculation, in tandem with the sorting, provides a running sum of the total

contribution so far of each store within the group.

6) With this Table section selected, insert another new Table section and drag all fields except

“Pcnt of Store Type” from the first Table section into this new Table section.

7) Double-click on the “Top 40% within Store Type” column to create a limit. Set the limit to

be less than or equal to 0.4 (the decimal equivalent of 40%).

1 2 ... 26 27 28 29 30 31 32 33 34 35 36 ... 77 78

Sin comentarios

Component Solution Services REC02F Manual de usuario Pagina 31