Conditional Column's calculation

[Anonymous]

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

[JonnyPoole]

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

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]

Hi Jonathan

I appreciate your help by providing very helpful answer.

Thanks lot

[JonnyPoole]

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

[Anonymous]

I do not see those buttons

[JonnyPoole]

Here is a link that may help with that

QlikCommunity Tip: How to get answers to your post?