Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

yonghanfi
New Contributor

How to sum up the value of two rows in one table during data transform

Hi, I have a very tricky table like below the green part shows. So when flag is one, the real value of Budge in each month is actually value of budge in current month sum up the budge in January. Anyone can help me achieve the data like in yellow part shows?

Thanks a lot!

   

cost center yearmonthflagbudge_typebudge Budget_Final
A201811ADR6565
A201821ADR267
A201831ADR166
A201841ADR065
A201851ADR368
A201861ADR-461
A201871ADR065
A201881ADR267
A201891ADR-263
A2018101ADR-164
A2018111ADR065
A2018121ADR-362
B201910Revenue10001000
B201920Revenue10051005
B201930Revenue10091009
B201940Revenue10031003
B201950Revenue10051005
B201960Revenue10001000
B201970Revenue10021002
B201980Revenue10301030
B201990Revenue10401040
B2019100Revenue10501050
B2019110Revenue10201020
B2019120Revenue10001000
B201911ADR7070
B201921ADR272
B201931ADR171
B201941ADR070
B201951ADR373
B201961ADR-466
B201971ADR070
B201981ADR272
B201991ADR-268
B2019101ADR-169
B2019111ADR070
B2019121ADR-367
1 Solution

Accepted Solutions
qliksus
Valued Contributor

Re: How to sum up the value of two rows in one table during data transform

May be try somethihg like

sum({<Month={"1"},flag={"1"}>} total<Year> Budge) + Sum(Budge)

5 Replies

Re: How to sum up the value of two rows in one table during data transform

Try this

Table:

LOAD * INLINE [

    cost center, year, month, flag, budge_type, budge

    A, 2018, 1, 1, ADR, 65

    A, 2018, 2, 1, ADR, 2

    A, 2018, 3, 1, ADR, 1

    A, 2018, 4, 1, ADR, 0

    A, 2018, 5, 1, ADR, 3

    A, 2018, 6, 1, ADR, -4

    A, 2018, 7, 1, ADR, 0

    A, 2018, 8, 1, ADR, 2

    A, 2018, 9, 1, ADR, -2

    A, 2018, 10, 1, ADR, -1

    A, 2018, 11, 1, ADR, 0

    A, 2018, 12, 1, ADR, -3

    B, 2019, 1, 0, Revenue, 1000

    B, 2019, 2, 0, Revenue, 1005

    B, 2019, 3, 0, Revenue, 1009

    B, 2019, 4, 0, Revenue, 1003

    B, 2019, 5, 0, Revenue, 1005

    B, 2019, 6, 0, Revenue, 1000

    B, 2019, 7, 0, Revenue, 1002

    B, 2019, 8, 0, Revenue, 1030

    B, 2019, 9, 0, Revenue, 1040

    B, 2019, 10, 0, Revenue, 1050

    B, 2019, 11, 0, Revenue, 1020

    B, 2019, 12, 0, Revenue, 1000

    B, 2019, 1, 1, ADR, 70

    B, 2019, 2, 1, ADR, 2

    B, 2019, 3, 1, ADR, 1

    B, 2019, 4, 1, ADR, 0

    B, 2019, 5, 1, ADR, 3

    B, 2019, 6, 1, ADR, -4

    B, 2019, 7, 1, ADR, 0

    B, 2019, 8, 1, ADR, 2

    B, 2019, 9, 1, ADR, -2

    B, 2019, 10, 1, ADR, -1

    B, 2019, 11, 1, ADR, 0

    B, 2019, 12, 1, ADR, -3

];


FinalTable:

LOAD *,

If([cost center] = Previous([cost center]) and year  = Previous(year) and budge_type = Previous(budge_type), RangeSum(Peek('Budget_Final'), budge), budge) as Budget_Final

Resident Table

Order By [cost center], budge_type, year, month;


DROP Table Table;

yonghanfi
New Contributor

Re: How to sum up the value of two rows in one table during data transform

Hi,

Very helpful, thanks a lot! But not exactly what I need. RangeSum can not apply, because final budget is sum of only current month and Month 1. For example for year 2018 and cost center A, final budget is 68 instead of 71.

Br,

Yong

Re: How to sum up the value of two rows in one table during data transform

Got it, try this

Table:

LOAD * INLINE [

    cost center, year, month, flag, budge_type, budge

    A, 2018, 1, 1, ADR, 65

    A, 2018, 2, 1, ADR, 2

    A, 2018, 3, 1, ADR, 1

    A, 2018, 4, 1, ADR, 0

    A, 2018, 5, 1, ADR, 3

    A, 2018, 6, 1, ADR, -4

    A, 2018, 7, 1, ADR, 0

    A, 2018, 8, 1, ADR, 2

    A, 2018, 9, 1, ADR, -2

    A, 2018, 10, 1, ADR, -1

    A, 2018, 11, 1, ADR, 0

    A, 2018, 12, 1, ADR, -3

    B, 2019, 1, 0, Revenue, 1000

    B, 2019, 2, 0, Revenue, 1005

    B, 2019, 3, 0, Revenue, 1009

    B, 2019, 4, 0, Revenue, 1003

    B, 2019, 5, 0, Revenue, 1005

    B, 2019, 6, 0, Revenue, 1000

    B, 2019, 7, 0, Revenue, 1002

    B, 2019, 8, 0, Revenue, 1030

    B, 2019, 9, 0, Revenue, 1040

    B, 2019, 10, 0, Revenue, 1050

    B, 2019, 11, 0, Revenue, 1020

    B, 2019, 12, 0, Revenue, 1000

    B, 2019, 1, 1, ADR, 70

    B, 2019, 2, 1, ADR, 2

    B, 2019, 3, 1, ADR, 1

    B, 2019, 4, 1, ADR, 0

    B, 2019, 5, 1, ADR, 3

    B, 2019, 6, 1, ADR, -4

    B, 2019, 7, 1, ADR, 0

    B, 2019, 8, 1, ADR, 2

    B, 2019, 9, 1, ADR, -2

    B, 2019, 10, 1, ADR, -1

    B, 2019, 11, 1, ADR, 0

    B, 2019, 12, 1, ADR, -3

];

Left Join (Table)

LOAD [cost center],

budge_type,

year,

FirstSortedValue(budge, month) as MinDateBudge

Resident Table

Group By [cost center], budge_type, year;

FinalTable:

LOAD *,

MinDateBudge- budge as Budget_Final

Resident Table;

DROP Table Table;

Capture.PNG

qliksus
Valued Contributor

Re: How to sum up the value of two rows in one table during data transform

May be try somethihg like

sum({<Month={"1"},flag={"1"}>} total<Year> Budge) + Sum(Budge)

Re: How to sum up the value of two rows in one table during data transform

I wonder why you posted your question in scripting portion when you needed a front end expression. I wasted my precious time . Just kidding....


anyways, is this even giving you what you wanted? I don't think so...

Capture.PNG


In case you want to get to the write expression, try this

Sum({<month={"1"}>} TOTAL <year, [cost center], budge_type> budge) + Sum({<month = {">1"}>}budge)

Capture.PNG

Community Browser