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: 
Anonymous
Not applicable

Conditional Column's calculation


Hi All

I have data like

LineNo  LineName Sales

1            A              10

2            B              20

3            C              30

4            D              40

5            E              50

And requirements is like below

Head          LineNos   Sales

Sales A          1           10

Sales B         2+4-3      30

Sales C         3+4+5-1  110

Sales D           2           20

Sales E           3           30

Sales F            4           40

....

and 200 lines like these where Sales are based on LineNos condition(Sum or subtraction only)

How do I create variable where IF special instruction (like for Sales B & Sales C) fulfilled ELSE can also fulfilled

Thanks in advance

5 Replies
JonnyPoole
Former Employee
Former Employee

A not bad way of managing this is to create a 'component multipliers' table that maps each LineNo used to calculate head Sales Head. Also include a multiplier (-1 or +1) to figure out if that LineNo in that Head is adding or subtracting from the total.

Then the expression in the chart is  sum(Sales * Multiplier)  , and you can get these results easily in straight / pivot table

Capture.PNG.png

LOAD LineNo,

    LineName,

    Sales

FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

ComponentMultipliers:

LOAD * INLINE [

    Head, LineNo, Multiplier

    A, 1, 1

    B, 2, 1

    B, 4, 1

    B, 3, -1

    C, 3, 1

    C, 4, 1

    C, 5, 1

    C, 1, -1

    D, 2, 1

    E, 3, 1

    F, 4, 1

];

Anonymous
Not applicable
Author

Hi Jonathan

I appreciate your help by providing very helpful answer.

Thanks lot

JonnyPoole
Former Employee
Former Employee

Sathish - thank you. Kindly mark any posts you find helpful  by designating them 'helpful'

Anonymous
Not applicable
Author

I do not see those buttons

JonnyPoole
Former Employee
Former Employee

Here is a link that may help with that

QlikCommunity Tip: How to get answers to your post?