topic Re: How to control expression based on dimension in QlikView

How to control expression based on dimension

jagannalla — Fri, 21 Jun 2013 10:20:59 GMT

Hi,

I've different number of product with there sales.

For eg:

Product, Sale

A,10

B,20

C,30

Now If I take straight table or pivot table with dimension as Product and expression as Sum(Sale). I can see result for each product sales. We don't have any data for D & E Products. But I need D product sales as A+B sales & E Product sales as C-A. Like this I've N number of calculated product columns. I don't want to control this using Above and Below function.

Is there any other technique to achive this ?

Thanks,
Jagan

Re: How to control expression based on dimension

Gysbert_Wassenaar — Fri, 21 Jun 2013 10:33:17 GMT

See attached example.

Re: How to control expression based on dimension

prodanov — Fri, 21 Jun 2013 10:46:10 GMT

In attached application I solved your problem in expression. See product AVG in line chart.

Re: How to control expression based on dimension

jagan — Fri, 21 Jun 2013 10:51:02 GMT

PFA file for solution.

Regards,

Jagan.

Re: How to control expression based on dimension

jagannalla — Fri, 21 Jun 2013 11:02:38 GMT

Thanks for all your techniques, let me check which one will be suitable for my main requirement.

Re: How to control expression based on dimension

jagan — Fri, 21 Jun 2013 15:24:29 GMT

Hi,

Try this script

Test:

LOAD * INLINE [

product,col1,col2

a,10,30

b,20,40];

Test1:

LOAD

Sum(col2) AS Col2Total

RESIDENT Test

WHERE match(product, 'a', 'b');

Test2:

LOAD

col1 * col2 as multipliedValue

RESIDENT Test

WHERE match(product, 'a', 'b')

Order by product;

LET vCol2Total = FieldValue('Col2Total', 1);

LET vProducta = FieldValue('multipliedValue', 1);

LET vProductb = FieldValue('multipliedValue', 2);

Concatenate(Test)

LOAD

'c' as product,

($(vProducta) / $(vCol2Total)) + ($(vProductb) / $(vCol2Total)) AS col1,

0 as col2

AUTOGENERATE 1;

DROP TABLEs Test1, Test2;

Regards,

Jagan.