# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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!

1 Solution

Accepted Solutions
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
MVP

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

Try this

Table:

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:

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;

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

MVP

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

Got it, try this

Table:

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)

budge_type,

year,

FirstSortedValue(budge, month) as MinDateBudge

Resident Table

Group By [cost center], budge_type, year;

FinalTable:

MinDateBudge- budge as Budget_Final

Resident Table;

DROP Table Table;

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)

MVP

## 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...

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)