Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
kdmarkee221
Contributor III
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.

money ok top 5.PNG

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

pctg NOT ok top 5.PNG

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)
5 Replies
marcus_sommer

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.

ali_hijazi
Partner - Master II
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
kdmarkee221
Contributor III
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.

marcus_sommer

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).  

ali_hijazi
Partner - Master II
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