Group data on a bar chart to create 'Others' category (and show data at 100%)

[#1]

Hi Community

A little time ago I receive your help when I needed to create a chart with Others category for non selected data (See http://community.qlik.com/message/220067)

I have a similar issue. I need to create the same chart, but the data has to be ponderated to 100%. I tried this approach, but I can't get a sucessful result

I create a variable (the same that was used on the first case)

='ValueList(' & Chr(39) & Concat(DISTINCT characteristic_value,Chr(39)&Chr(44)&Chr(39)) & Chr(39) & ',' & Chr(39) & 'Others' & Chr(39) & ')'

On the chart:

Dimesionns:

1.- Calculated dimesion

=$(vValueList)

2.- Regular date dimension

Expression

=if($(vValueList) = 'Others',
          sum({<characteristic_value=E()>} fact_sold_units)/Sum({1<cal_monthyear>} fact_sold_units),
          sum(if(characteristic_value=$(vValueList), fact_sold_units))/Sum({1<cal_monthyear>} fact_sold_units)
)

And the result is shown in the attached image

The expected result would be all  the bars ponderated to 100%. Is there any problem with the formmulas used?

Any help will be appreciated

Thanks

(I have personal edition of qlikview, so I can't see external fies )

[#2]

im not sure i got what you mean but try to check this:

=if($(vValueList) = 'Others',
          sum({<characteristic_value=E()>} fact_sold_units)/Sum({1} total fact_sold_units),
          sum(if(characteristic_value=$(vValueList), fact_sold_units))/Sum({1}total  fact_sold_units)
)

[#3]

Thanks Pari Pari

I tried with your suggestion, but is didn't work.

I continued trying to find a solution to my problem, and I think i found it

I use this formula on the expresion

=if($(vValueList) = 'Others',
      sum({<characteristic_value=E()>} fact_sold_units) / Sum({<characteristic_value=>} TOTAL <cal_monthyear,                  characteristic_type> fact_sold_units) * ($(count_vValueList)),
      sum(IF(characteristic_value=$(vValueList),fact_sold_units)) / Sum({<characteristic_value=>} TOTAL                <cal_monthyear, characteristic_type> fact_sold_units) * ($(count_vValueList))
)

in where the new variable count_vValueList is

=Count($(vValueList))

I created this variable becaude when I use directly Count($(vValueList)) on the formula it didn't work (Any idea why?)

Well, I multiplied by the amount of total selected categorires, because when I select 4 values + the 'Others' I obteined total od 20 %. If I selected 3 + 'Others' I obtained 25%. So, multiplying by total amount (20*5 or 25 *4) I get desired 100%

I'm not sure why this happened, so would be great if someone can explain me

Thanks a lot