How to control expression based on dimension

[jagannalla]

Hi,

I've different number of product with there sales.

For eg:

Product, Sale

A,10

B,20

C,30

D,

E,

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

[Gysbert_Wassenaar]

See attached example.

[prodanov]

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

[jagan]

PFA file for solution.

Regards,

Jagan.

[jagannalla]

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

[jagan]

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.