topic Re: previous amount compared to current amount in QlikView

previous amount compared to current amount

Anonymous — Mon, 18 Apr 2016 15:08:23 GMT

Hi Guys,

I need a little help.

I'm trying to do the following.

I want to calculate the amount of the previous month and subtract that to the amount of the current month.

For example.

In january the amount was 31. Since there is no previous month, null is returend.

In februari the amount was 56. So if I would select february I would get 56 - 31 (month januari) = 45.

In march the amount was 93. So 93 - 56 (month february) = 37

I want to solve this in my script, but also want to see it how it's done in set analysis.

Please find an example of my script.

Thanks!

Sam

Edited: 03-05-2016:

Please have a look at the attached files.

Re: previous amount compared to current amount

sunny_talwar — Mon, 18 Apr 2016 15:12:50 GMT

Something like this?

Sum(Aggr(Above(Sum({<month>}beginSaldo)), month)) - Sum(beginSaldo)

Re: previous amount compared to current amount

Anonymous — Mon, 18 Apr 2016 15:19:28 GMT

Thanks! I dont want to solve it in my chart. I want to solve it in my script.

Re: previous amount compared to current amount

sunny_talwar — Mon, 18 Apr 2016 15:25:33 GMT

Can you elaborate how you imagine to see these values?

Re: previous amount compared to current amount

Anonymous — Mon, 18 Apr 2016 15:36:30 GMT

do you mean, how I want the end result to be?

For example.

In january the amount was 31. Since there is no previous month, null is returend.

In februari the amount was 56. So if I would select february I would get 56 - 31 (month januari) = 45.

In march the amount was 93. So 93 - 56 (month february) = 37

In other words if I select the current month, I get the amount of the current month and of the previous month.

Hope this clarifys

Re: previous amount compared to current amount

sunny_talwar — Mon, 18 Apr 2016 16:44:24 GMT

May be this?

Table:

LOAD *,

MonthName(date) as MonthYear;

LOAD date,

amount

FROM

amount.xlsx

(ooxml, embedded labels, table is page1);

AggrTable:

LOAD *,

If(MonthYear = AddMonths(Peek('MonthYear'), 1), Total - Peek('Total'), Null()) as Diff;

LOAD MonthYear,

Sum(amount) as Total

Resident Table

Group By MonthYear

Order By MonthYear;

Re: previous amount compared to current amount

Anonymous — Tue, 03 May 2016 10:00:37 GMT

Hi,

I created a dataset, trying the same as you did above. But without succes.

I'm also trying to make a cumulative of all the transaction per customer.

I dont know where it goes wrong. Is it the expression or the 'Order By' statement?

Where can I upload files to the forum?

Regards,

Sam

Re: previous amount compared to current amount

sunny_talwar — Tue, 03 May 2016 10:01:31 GMT

Check this link: Uploading a Sample

Re: previous amount compared to current amount

Anonymous — Tue, 03 May 2016 11:45:55 GMT

I've edited the original file.

Please have a look at the file.

I'm trying to accumulate all the transactions per customer.

And have a column that shows the difference. between the previous amount and the current amount.

if the debt has not being payed fully, then 'still in debt', else 'no more debt'.

Hope you can help me

cheers!

Sam

Re: previous amount compared to current amount

Anonymous — Tue, 03 May 2016 13:29:46 GMT

Hi Sunny,

Where u able to look at the problem? I'm stuck and really need help .

Many thanks in advance!

Regards,

Sam