Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikhans
Creator
Creator

convert YTD to monthly movements

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

1 Solution

Accepted Solutions
sunny_talwar

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;

Capture.PNG

View solution in original post

6 Replies
ejvanmastrigt
Partner - Contributor II
Partner - Contributor II

Hi Hans,

Please find the file attached. This will probably help.

Best regards,

Erik

sunny_talwar

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;

Capture.PNG

Anonymous
Not applicable

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

Anonymous
Not applicable

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

qlikhans
Creator
Creator
Author

Hi erik,

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

qlikhans
Creator
Creator
Author

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.