manipulating data in table

[#1]

Hi everyone,

I have a simple table with 3 fields:

Year	Month	Payment
2010	3	500
2010	4	500
2010	5	500
2010	6	500
2010	7	500
2010	8	500
2010	9	500
2010	10	500
2010	11	500
2010	12	500
2011	1	500
2011	2	500
2011	3	500
2011	4	500

What I'm trying to do in Qlik is get the total balance (7000) then reduce the balance by the monthly payment, so the table will end up looking like this:

Year	Month	Balance
2010	3	6500
2010	4	6000
2010	5	5500
2010	6	5000
2010	7	4500
2010	8	4000
2010	9	3500
2010	10	3000
2010	11	2500
2010	12	2000
2011	1	1500
2011	2	1000
2011	3	500
2011	4	0

Though I'm fairly new to Qlik and am not sure of the best way to do this, if anyone can point me in the right direction it would be much appreciated.

Kind Regards,
Phil

[spsrk_84]

Hi Phil,

I think u might have used the expression like this i.e TOTAL BALANCE,instead of this u try with this

Sum(Payment).Try this one in cse if u find any problem u please attach the sample file then i can solve ur problem.

Rgds,

ajay

[#3]

Hi Ajay,

Many thanks for the reply.

I have 2 dimensions (year,month) and only one expression (payment) from this I'm trying to calculate the balance in Qlik.

I'm guessing I need to use set analysis or aggregation but am struggling at the moment... I can't even get a running sum for the payment at the moment 😞

Thanks,
Phil

[#4]

Hi Phil,

You can try the following. This will give you what you need at load time. I hope this is what you need.

pymt_tmp:
LOAD * INLINE [
PYear, PMonth, PPayment
2010, 3, 500
2010, 4, 500
2010, 5, 500
2010, 6, 500
2010, 7, 500
2010, 8, 500
2010, 9, 500
2010, 10, 500
2010, 11, 500
2010, 12, 500
2011, 1, 500
2011, 2, 500
2011, 3, 500
2011, 4, 500
];

A1:
load PYear as Year1, PMonth as Month1, PPayment as Payment1 , 1 as PK
resident pymt_tmp;

drop table pymt_tmp;

A2:
load PK, sum(Payment1) as TPayment2
resident A1
GROUP BY PK;

A3:
Left join (A1)
load TPayment2 as TPayment3
resident A2;

pymt:

load Year1 as Year3, Month1 as Month3, (TPayment3 - (Payment1 * rowno())) as Payment3
resident A1;

drop table A2

This will create two table, you can ignor table A1, the table Pymt is the one what you need.

Regards,

Mukund

[#5]

Hi Mukund,

Many thanks for the reply, the code you supplied is really helpful for something else I'm working on 🐵

For this though I need to keep the underlying data at the payment level and calculate the balance in the chart... as there's a lot of underlying data that builds up to the totals.

I found something on the Qlik help for chart inter record functions and used the following to create an expression called Payment:

if(rowno()=1,sum( {1} Total Payment ), above( Balance ) - Payment )

Kind Regards,
Phil