Substracting two rows

[Anonymous]

Hi,

I have an requirement which says to subtract the two date's data and stored in a new column.

Eg For Supplier

5/14  7      -

5/21  7      7-7 = 0

5/28  7       7-7 = 0

Attached is the excel file.

Thanks in advance!!

Thanks and Regards,

Drishti Goel

[Frank_Hartmann]

Script:

LOAD MFG,
     MPN,
     [Customer Part Number],
     Date,
     Arrow,
     Supplier,
     Arrow - Supplier as Diff
FROM

(ooxml, embedded labels, table is Sheet1);

Output:

But as an alternative you can also make the calculation in a straight stable with Date as dimension and an expression:

sum(Arrow) - sum(Supplier)

[MarcoWedel]

Within your sample data there are multiple fields containing a value of 7 in multiple consecutive rows.

Do you have a more unambiguous example?

[Anonymous]

hey thanks for your  time to reply for my post. My requirement is to find the out the difference of the values among the rows of the date for each supplier and arrow separately, You can refer to the column Q and R.

[Anonymous]

Eg :

Date  Parts Arrow Supplier Weekly Change

                                             Arrow  Supplier

6/12    1       7          8            0            0

6/18    2       7          8          7-7=0       8-8=0

6/24    3       8          9          8-7=1        9-8=1

[Anonymous]

We're trying to find out the weekly trend and that's the reason why it has more ambiguous data

[MarcoWedel · Accepted solution]

Hi,

now that you clarified with another example one solution might be:

table1:
LOAD RecNo() as ID,
    MFG,
    MPN,
    [Customer Part Number],
    Date#(Date,'M/D') as Date,
    Arrow,
    Supplier,
    If(MFG&'/'&MPN=Previous(MFG&'/'&MPN),Arrow-Previous(Arrow),0) as [Weekly Arrow],
    If(MFG&'/'&MPN=Previous(MFG&'/'&MPN),Supplier-Previous(Supplier),0) as [Change Supplier]
FROM [https://community.qlik.com/servlet/JiveServlet/download/1503036-328924/datasets_1.xlsx] (ooxml, embedded labels, table is Sheet1);

hope this helps

regards

Marco

[Anonymous]

Thank you, this is exactly I want it

[MarcoWedel]

glad it worked for you.