topic Re: Combining dimensions into one in chart in QlikView

Combining dimensions into one in chart

jessica_webb — Wed, 25 Nov 2020 16:16:04 GMT

I have the following data table:

ID	Target	Current
32456	1	3
9754	5	2
3215	6	4
7512	2	7
3254	4	5

'Target' and 'Current' share the same values.

How can I create a chart like this, where I have COUNT(ID) as the expression, and the two dimensions as one?

Re: Combining dimensions into one in chart

qliksus — Fri, 21 Jul 2017 08:51:41 GMT

May be something like this

Load ID,

Target,

Current ,

Subfield(Key,'-') as Dim ;

Load

ID,

Target,

Current ,

Target&'-'&Current as Key

from table ;

Now use this dim in the chart for your calculation

Re: Combining dimensions into one in chart

jessica_webb — Fri, 21 Jul 2017 08:56:46 GMT

Thanks for the response. Would there be anyway to do this directly in the chart?

I'm having real troubles getting a new dimension into the script:

Re: Reload data with renamed fields

Re: Combining dimensions into one in chart

qliksus — Fri, 21 Jul 2017 10:27:37 GMT

Maybe something like this

Dimension: ValueList($(=Concat(DISTINCT Current ,',')&','&Concat(DISTINCT Target ,',')))

Expression : count(distinct if(Current=ValueList($(=Concat(DISTINCT Current ,',')&','&Concat(DISTINCT Target ,','))), ID)) +

count(distinct if(Target=ValueList($(=Concat(DISTINCT Current ,',')&','&Concat(DISTINCT Target ,','))), ID))

Re: Combining dimensions into one in chart

rahulpawarb — Fri, 21 Jul 2017 10:44:02 GMT

Hello Jessica,

May be try below calculated dimension:

=Pick(Match($Field,'Target','Current'),[Target],[Current])

Regards!

Rahul Pawar

Re: Combining dimensions into one in chart

jessica_webb — Fri, 21 Jul 2017 12:13:29 GMT

Thanks Rahul, but I still want to keep the 'Target' and 'Current' dimensions showing separately in the chart.

In your example, they are grouped together into one bar.

Re: Combining dimensions into one in chart

jessica_webb — Fri, 21 Jul 2017 12:14:34 GMT

This looks as though it is grouping the two dimensions into one... I would like them to still show side by side.

Re: Combining dimensions into one in chart

Fri, 21 Jul 2017 12:24:07 GMT

can you explain what the dimension and expressions are, that will help in finding what you need and to give solution.

Re: Combining dimensions into one in chart

Kushal_Chawda — Fri, 21 Jul 2017 12:28:07 GMT

like this

Data:

LOAD ID,

Target as Value,

'Target' as Flag

FROM

[https://community.qlik.com/thread/268478?sr=stream&ru=186120]

(html, codepage is 1252, embedded labels, table is @1);

Concatenate

LOAD ID,

Current as Value,

'Current' as Flag

FROM

[https://community.qlik.com/thread/268478?sr=stream&ru=186120]

(html, codepage is 1252, embedded labels, table is @1);

Create Bar chart

Dimension:

Value

Flag

Expression:

Count (ID)

Re: Combining dimensions into one in chart

jessica_webb — Fri, 21 Jul 2017 12:33:46 GMT

Hi Krishna,

The dimensions are in the table here (so ID, Target, Current)

ID	Target	Current
32456	1	3
9754	5	2
3215	6	4
7512	2	7
3254	4	5

The expression is Count(ID)

So the table/Chart outcome I would want is:

Count	Dimension 1	Dimension 2
1	1	Target
0	1	Current
1	2	Target
1	2	Current
0	3	Target
1	3	Current
1	4	Target
1	4	Current
1	5	Target
1	5	Current
1	6	Target
0	6	Current
0	7	Target
1	7	Current

Re: Combining dimensions into one in chart

jessica_webb — Fri, 21 Jul 2017 12:34:18 GMT

Thanks for the response. Would there be anyway to do this directly in the chart?

I'm having real troubles getting a new dimension into the script:

Re: Reload data with renamed fields

Re: Combining dimensions into one in chart

oscar_ortiz — Fri, 21 Jul 2017 12:46:12 GMT

Jessica,

Are you trying to produce something like this?

Good luck

Oscar

Re: Combining dimensions into one in chart

jessica_webb — Fri, 21 Jul 2017 12:48:58 GMT

Hi Oscar,

Thank you, but not quite...

The values for target and current aren't countable numbers - they're nominal.

So for example:

ID	Target	Current
32456	A	C
9754	E	B
3215	F	D
7512	B	G
3254	D	E

So what I need is a count of each instance - not a sum...

Re: Combining dimensions into one in chart

Kushal_Chawda — Fri, 21 Jul 2017 13:09:09 GMT

Hi Jessica,

As per your requirement you need to change the structure of your data through script, that means it will be quite complex to do this on front end, perhaps I don't think that will be possible on front end.

Re: Combining dimensions into one in chart

oscar_ortiz — Fri, 21 Jul 2017 13:09:48 GMT

Maybe something like this then?

Good luck

Oscar

Re: Combining dimensions into one in chart

qliksus — Fri, 21 Jul 2017 13:27:38 GMT

Just tweak the expression and add one more dimension

Dimension1: ValueList($(=Concat(DISTINCT Current ,',')&','&Concat(DISTINCT Target ,',')))

Dimension2 : =ValueList('Target','Current')

Expression : count(distinct if(Current=ValueList($(=Concat(DISTINCT Current ,',')&','&Concat(DISTINCT Target ,','))) and ValueList('Target','Current')='Current' , ID)) +

count(distinct if(Target=ValueList($(=Concat(DISTINCT Current ,',')&','&Concat(DISTINCT Target ,','))) and ValueList('Target','Current')='Target', ID))

Re: Combining dimensions into one in chart

jessica_webb — Fri, 21 Jul 2017 13:33:37 GMT

Would you be able to provide me with your sample file that you did this in?

I've tried copying it into my own application, but it doesn't work for me...

Re: Combining dimensions into one in chart

jessica_webb — Fri, 21 Jul 2017 13:58:48 GMT

But the data in your chart is incorrect. For example, you're showing a count of 1 for 'Target - C', but that should be 0

Re: Combining dimensions into one in chart

qliksus — Mon, 24 Jul 2017 03:51:46 GMT

Attaching is restricted in my network so pasting the screenshot of what I have done can you post the error you are getting ?