Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 )
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)
)
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