topic Re: Manipulating / Custom Dimension in Chart using ValueList in QlikView

Manipulating / Custom Dimension in Chart using ValueList

shaan007 — Wed, 22 Oct 2014 22:19:51 GMT

Dimension

Field Name : Group

Dimension:	Expression:
Group	=Sum(Value)
A	200
B	300
C	500
D	1500

The Group D sharing 60% as D1 and 40% D2. I want the count(value) to split the D into D1 and D2 and show that in a chart.

Dimension:		Expression:
Group		=Sum(Value)
A		200
B		300
C		500
D	D1	900
	D2	600

I want the chart to show that as the attached image.

please anyone help.

Message was edited by: Prasanna Balachandran

Re: Manipulating / Custom Dimension in Chart using ValueList

jagan — Thu, 23 Oct 2014 05:29:42 GMT

Hi,

This is not possible in front end, instead you try in backend like this

DataTemp:

LOAD

Group,

Value

FROM DataSource;

LEFT JOIN

LOAD

INLINE [

Group, DummyDim

D, D1

D, D2

];

Data:

NoConcatenate

LOAD

Group,

DummyDim,

Value * If(DummyDim = 'D1', 0.6, If(DummyDim = 'D2', 0.4, 1) AS Value

Resident DataTemp;

DROP TABLE DataTemp;

Now in chart just use

Dimension: Group, DummyDim

Expression: Sum(Value)

Hope this helps you.

Regards,

Jagan.

Re: Manipulating / Custom Dimension in Chart using ValueList

tresB — Thu, 23 Oct 2014 06:02:34 GMT

Basic logic(which could be optimized, may be using variables) is something like:

Dimension: =ValueList('A','B','C','D1','D2')

Expression:

Pick(Match(ValueList('A','B','C','D1','D2'), 'A','B','C','D1','D2'),

Sum({<[Dimension:]={"A"}>}[Expression:]),

Sum({<[Dimension:]={"B"}>}[Expression:]),

Sum({<[Dimension:]={"C"}>}[Expression:]),

Sum({<[Dimension:]={"D"}>}[Expression:])*0.6,

Sum({<[Dimension:]={"D"}>}[Expression:])*0.4

)

PFA

Re: Manipulating / Custom Dimension in Chart using ValueList

shaan007 — Thu, 23 Oct 2014 22:52:18 GMT

Thank you Tressco

Only Problem is... if the Group Filed get updated(example add new groups such as 'F', 'G'

then I have to Update the expression too.

anyways thanks a lot

Re: Manipulating / Custom Dimension in Chart using ValueList

shaan007 — Thu, 23 Oct 2014 22:54:23 GMT

Perfect, let me test this and get back to u soon.

Re: Manipulating / Custom Dimension in Chart using ValueList

jagan — Fri, 24 Oct 2014 01:45:42 GMT

Hi,

Is above solution worked for you?

Regards,

Jagan.

Re: Manipulating / Custom Dimension in Chart using ValueList

shaan007 — Wed, 29 Oct 2014 21:58:19 GMT

Hi jagan, ur approach is working, Only problem is the Legend,

Re: Manipulating / Custom Dimension in Chart using ValueList

shaan007 — Wed, 29 Oct 2014 22:33:59 GMT

HI Tresco,

I encounter another issue while using your method.

selection on chat is not working if i use value List.

I do understand why this is happening. Any suggestion or tricks?

Cannot Do Selection in Chart if use ValueList in Dimension

Re: Manipulating / Custom Dimension in Chart using ValueList

jagan — Thu, 30 Oct 2014 01:45:53 GMT

Hi,

ValueList is a Synthetic Dimension so it is not possible to filter, try like this

Chart Properties -> Dimensions -> Select Supress When Value is Null for DummyDim.

Hope this helps you.

Regards,

Jagan.

Re: Manipulating / Custom Dimension in Chart using ValueList

shaan007 — Thu, 30 Oct 2014 02:37:45 GMT

Nandri for the reply Jagan.

Suppress When Value is Null for DummyDim didn't work, It will hide all other A,B,C .

I need a way to edit or customize the Legend.

Or else I may use text Object .

thank you

Re: Manipulating / Custom Dimension in Chart using ValueList

jagan — Thu, 30 Oct 2014 02:44:27 GMT

Hi,

Create an image for legend and place it on top of the chart, this way you can create the custom legend.

Regards,

Jagan.

Re: Manipulating / Custom Dimension in Chart using ValueList

shaan007 — Thu, 30 Oct 2014 05:24:17 GMT

thank you