Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Group data on a bar chart to create an 'Others' category

HI all.

I'm a newbie on the Qlikview World, so I really need your help

I've been looking on the forums and I can't found an exact answer to my problem.

I have a bar chart and a dimension called "Values". When I selected items from "Values", they appears on the chart. My problem is that besides the selected data I need to add a new one called 'Others', that contains the summatory of the non selected data.

I found that expression

if(dimensionality(),sum({<product =e(product)>}sale),sum(sale))

With this, the selected values are not shown on the chart, only the non selected (great, I need this), but I don't know how to combine to obtain the selected data and the 'Others' in the same chart

Thanks a lot

1 Solution

Accepted Solutions
Jason_Michaelides
Luminary Alumni
Luminary Alumni

Sorry for the delayed answer mate - I had my expression slightly wrong.  Instead of:

if($(vValueList) = 'Others',

sum({<characteristic_value=E()>} fact_sold_units),

sum(fact_sold_units)

)

you need:

if($(vValueList) = 'Others',

sum({<characteristic_value=E()>} fact_sold_units),

sum(IF(characteristic_value=$(vValueList),fact_sold_units))

)

See attached,hope it helps,

Jason

View solution in original post

15 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

Check out Oleg T's contribution here:

http://community.qlik.com/thread/4731

Should help you.

Not applicable
Author

I has seen that thread. I tried to replicate that solution, but I only can selected one value, if I try with more than one, no values are shown :s

On that solution, this formula appears to calculate the dimension

=ValueList('$(=only(Vendor))', 'Others')

I tried modifying to

=ValueList(GetFieldSelections(characteristic_value, ','), 'Others')

but the selected values appears grouped in one line,  not in different categories.

Any ideas?


Jason_Michaelides
Luminary Alumni
Luminary Alumni

Try replacing the =Only(Vendor) with =(Concat(Distinct Vendor,','))

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Or use =GetFieldSelections(Vendor,',')

Only replace the =Only(Vendor) bit - the rest should stay the same!

Not applicable
Author

I tried both options, but the categories appear group in the chart (see attached file)

Screen shot 2012-05-17 at 11.01.32 AM.png

I need that 'basket', 'outdoor', 'running' and any other selected category appears separately

Thansk in advance

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Can you post your app?

Not applicable
Author

Here is the file

I need to achieve the same that in the "Chart 1", but grouping all not selected "values" on a category call "Others". I try to do that on the "Chart 2".

BTW, I know that the expression on chart 2 needs to be modified, but for know I want to fix the displa y of categories on the chart

I'm using a personal version of qlikview (I don't know if this will be  a problem)

Thanks

Not applicable
Author

I'm using a personal version of qlikview, so I'm not sure if this will be a problem to see my file

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Got it working.  ValueList() is very strict about what it receives and Concat() doesn't directly work.  Instead I have created a variable vValueList that builds up a ValueList() expression.  Then, in your dimension just used $(vValueList) to evaluate the expression.  This is a common approach for certain dynamic set anlaysis expressions too.  vValueListis:

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

which renders to:

ValueList('Basket','Female','Futbol','Others')

which evaluates to the list.  Updated app attached.

Cheers,

Jason