topic Re: Show unique values in pivot table in QlikView

Show unique values in pivot table

Mon, 26 Oct 2015 08:57:57 GMT

I have a pivot table showing 'Ideas' at various Statuses (x-axis dimension) by Region (y-axis dimension).

However it is only showing one Idea per region and stage, when I know for some there are multiple Ideas.

My expression is very simple:

=Ideas.Idea.Title

As are my dimensions, which simple list the field name. The are not calculated in any way.

How can i show ALL the ideas at each Status and Region?

Re: Show unique values in pivot table

jsingh71 — Mon, 26 Oct 2015 09:11:11 GMT

Tell me

"Ideas.Idea.Title" is your fieldname?

Re: Show unique values in pivot table

morganaaron — Mon, 26 Oct 2015 09:26:21 GMT

That's the nature of showing dimensions in the way you are - unless there's a unique dimension value for each idea they'll be grouped and your expression will only show for region/stage combos that have only one potential value.

You could try use the concat() function which will give you all ideas for each combo, but this will format it inside of one cell, so if you have a lot of values it may not be a clear representation.

You'd need to add an ID dimension to return a unique value.

Re: Show unique values in pivot table

Mon, 26 Oct 2015 09:34:49 GMT

Yes

Re: Show unique values in pivot table

Digvijay_Singh — Mon, 26 Oct 2015 09:49:23 GMT

You can try Concat(Idea field,',') as expression. If you have multiple values per region and status, it needs to be concatenated. Single field name in expression if results into multiple different values will show null in the output.

OR try Aggr(Concat(Idea filed,','),Region,status)

I think the first one should work if we have taken Region and status as dimensions in the chart.

Thanks,

Re: Show unique values in pivot table

Mon, 26 Oct 2015 09:52:56 GMT

OK, i have tried what you suggested and it works, but it's not ideal

I don't want the Idea Number dimension showing, nor do I want one row for each idea,as this make the table far too big

Re: Show unique values in pivot table

Mon, 26 Oct 2015 09:54:25 GMT

Can i insert a line break, rather than a comma?

Re: Show unique values in pivot table

Digvijay_Singh — Mon, 26 Oct 2015 09:56:36 GMT

try chr(10)

Re: Show unique values in pivot table

morganaaron — Mon, 26 Oct 2015 09:58:26 GMT

The other option as mentioned is using Concat. You could use Concat([Idea], chr(10)) and then wrap the cell height to the max number of ideas per combination to show this in a similar idea without needing the ID field.

Re: Show unique values in pivot table

Mon, 26 Oct 2015 10:00:40 GMT

That worked, but I now need to colour Ideas which have the Priority field set as 'High' red.

Is that possible within the same cell? If not, is there a different solution?

Re: Show unique values in pivot table

jsingh71 — Mon, 26 Oct 2015 10:05:10 GMT

For color write your expression as per below image:

Re: Show unique values in pivot table

morganaaron — Mon, 26 Oct 2015 10:06:48 GMT

This would only work if all your ideas within the stage/region combination had the same priority. There's no way to do that unless each idea had a unique cell unfortunately.

Re: Show unique values in pivot table

Mon, 26 Oct 2015 10:07:15 GMT

Even for multiple rows or text IN THE SAME CELL?

Re: Show unique values in pivot table

Digvijay_Singh — Mon, 26 Oct 2015 10:07:59 GMT

I don't think you can color text of individual ideas in one cell.But you can color the complete cell by adding condition in the background color(thru '+' sign below the expression). I think this won't work if some of ideas are of high priority in same cell.

Not sure of color differences in same cell.

Re: Show unique values in pivot table

Clever_Anjos — Mon, 26 Oct 2015 10:08:22 GMT

try Concat(distinct Ideas.Idea.Title)

Re: Show unique values in pivot table

Mon, 26 Oct 2015 10:13:57 GMT

That puts them in the same cell Celever.

Unfortunately i need to format each idea cell differently

Re: Show unique values in pivot table

Digvijay_Singh — Mon, 26 Oct 2015 10:20:32 GMT

try Aggr(Only(Ideas),Region,status)

Not sure but this may put each idea in different cell, not sure though..

Re: Show unique values in pivot table

Mon, 26 Oct 2015 10:24:36 GMT

That only shows a combination of Region/Status that has one idea.

If there are multiple ideas for a particular Region/Status it doesn't show that cell/column at all!

Re: Show unique values in pivot table

Mon, 26 Oct 2015 10:43:04 GMT

I'm so close! This is how I need the final table to look, BUT with each idea in a separate cell

In this case, I have simply Concatenated the ideas and inserted a line break

Is there no way this can be done?

Re: Show unique values in pivot table

Digvijay_Singh — Mon, 26 Oct 2015 10:47:16 GMT

I think you need to include all three fields as dimension so that each idea can appear in different line( which you don't want as you said initially to keep the chart short) but if there is no option suggested from others as well, you can think of going with it with a list box of priority, filtering red,orange, green only based on selection.

You can have one value always selected in list box( through list box property) so that chart shows important entries only while launching.