Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
jagannalla
Partner - Specialist III
Partner - Specialist III

How to control expression based on dimension

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

5 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

See attached example.


talk is cheap, supply exceeds demand
prodanov
Partner - Creator
Partner - Creator

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

jagan
Partner - Champion III
Partner - Champion III

PFA file for solution.

Regards,

Jagan.

jagannalla
Partner - Specialist III
Partner - Specialist III
Author

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

jagan
Partner - Champion III
Partner - Champion III

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.