Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
Contributor III

## Showing Top 5 with Others as percentage for a series of months

I need to get a Top 5 (and Others) working with percentages of totals as a straight table (pivot would be ideal if it is even possible) and a stacked bar chart.

I can accomplish the top 5 (and Others) via the Dimension Limits feature for the dollar amounts as shown below.

But I really want to report on the percentages (per monthly totals) as shown below but cannot get it working.

My expression for the dollars is:

sum({\$<TableKey = {'ABC'}, TYPE={'Charges'}, ROLLING12_TIMEPERIOD_SD={'Current'}>}AMOUNT)

My expression for the percentage, which does not work, is:

sum({\$<TableKey = {'ABC'}, TYPE={'Charges'}, ROLLING12_TIMEPERIOD_SD={'Current'}>}AMOUNT)
/
sum(TOTAL{\$<TableKey = {'ABC'}, TYPE={'Charges'}, ROLLING12_TIMEPERIOD_SD={'Current'}>}AMOUNT)

I appreciate any help I can get.  Thanks.

Labels (1)
• ### Layout & Visualizations

5 Replies

You may need to play a bit more with the TOTAL statement maybe by adding dimensions to it like:

TOTAL <Year, Month>

to ignore not the entire dimensions else considering further the listed ones. Helpful may also be not check this in the rate calculation else to use the total ones as a second expression which makes it much easier to comprehend the results - if it worked on the partial level you could finally adjust the final view.

Partner - Master II

I assume you need to amend the expression of percentage as follows:
sum({\$<TableKey = {'ABC'}, TYPE={'Charges'}, ROLLING12_TIMEPERIOD_SD={'Current'}>}AMOUNT)
/
sum(TOTAL<[Service Month],[Service Year]>{\$<TableKey = {'ABC'}, TYPE={'Charges'}, ROLLING12_TIMEPERIOD_SD={'Current'}>}AMOUNT)
Assuming that Service Month and Service Year are the names of the dimension and not labels in the chart (unless they are the same)

I can walk on water when it freezes
Contributor III
Author

I have been experimenting with TOTAL<xxx> and was not getting anywhere.  I can report however that I did get this working but I'm not sure why this mattered.  Instead of using 2 separate fields in my objects and expressions etc ([Service Year] and [Service Month]), I used a single field called [Month & Year], then used TOTAL<[Month & Year]> and it worked.

In general worked TOTAL with n listed dimensions and also the order of the listing has no impact. Therefore the cause must be an applying of a wrong dimensions maybe by calling the label and not the field-name or a similar field which is not the used as object-dimension.

Further important to TOTAL statements and also aggr-dimensions or set analysis-conditions - it must be native fields from the data-model and not calculated dimensions, like: month(MyDate).

Partner - Master II

In the total<x,y,z> the x,y, and z should be available in the dimensions of the chart

You cannot use a dimension outside the chart

I can walk on water when it freezes
Community Browser