convert YTD to monthly movements

[qlikhans]

Hi all

please see attached excel sheet for visualisation

each month I receive as input crosstables with year to date amounts

I convert these cross table to a regular table

next step is that I want to see the monthly movements

preferred handling is in back end

all suggestions are welcome

thanks in advance

[ejvanmastrigt]

Hi Hans,

Please find the file attached. This will probably help.

Best regards,

Erik

[sunny_talwar · Accepted solution]

Try this script

Table:

CrossTable (Category, AmountYTD, 2)

LOAD * INLINE [

    Date, description, category 1, category 2, category 3

    1/31/2017, A, 10, 20, 30

    1/31/2017, B, 15, 25, 35

    1/31/2017, C, 20, 30, 40

    2/28/2017, A, 25, 35, 45

    2/28/2017, C, 30, 40, 50

    3/31/2017, A, 35, 45, 55

    3/31/2017, B, 40, 50, 60

    3/31/2017, C, 45, 55, 65

    3/31/2017, D, 50, 60, 70

    4/30/2017, A, 55, 65, 75

    4/30/2017, C, 60, 70, 80

];

TempTable:

LOAD AutoNumber(Date&description&Category) as Key,

*

Resident Table;

DROP Table Table;

Temp:

LOAD description,

Category

Resident TempTable;

Join (Temp)

LOAD Distinct Date

Resident TempTable;

Concatenate (TempTable)

LOAD *

Resident Temp

Where not Exists(Key, AutoNumber(Date&description&Category));

FinalTable:

LOAD Date,

description,

Category,

AmountYTD,

If(description = Previous(description) and Category = Previous(Category), RangeSum(AmountYTD, -Previous(AmountYTD)), AmountYTD) as Amount

Resident TempTable

Order By description, Category, Date;

DROP Table TempTable, Temp;

[Anonymous]

Hey! try this out! i worked starting from the cross table

[Anonymous]

everything is made in backend, by creating a field for each date, containing the monthly movement

[qlikhans]

Hi erik,

t.u. for sending in. Can't open it since I am on QV and not on QS

[qlikhans]

Hi Raffaele,

Thanks for giving this solution. It works, so from that perspective it is a correct solution. The code however is out of my league, and when you do not understand it: do not use it.