topic Re: How to count that? in QlikView

How to count that?

stantrolav — Wed, 18 Dec 2013 08:28:19 GMT

How can i count [Amount for date]:

Agreement	Date	Amount	Amount for date
1302	25.01.2013	33999,07	167897,57
1302	31.01.2013	-420,93	133898,5
1302	26.02.2013	33835,26	134319,43
1302	28.02.2013	-105,19	100484,17
1302	25.03.2013	33574,91	100589,36
1302	31.03.2013	-210,67	67014,45
1302	25.04.2013	33700,58	67225,12
1302	30.04.2013	-87,8	33524,54
1302	27.05.2013	33612,34	33612,34
1303	26.02.2013	56769,77	113209,12
1303	28.02.2013	-55,3	56439,35
1303	25.03.2013	56494,65	56494,65
1304	41269	115212,56	460674,63
1304	30.01.2013	-174,66	460674,63
1304	31.01.2013	115249,02	345462,07
1304	28.02.2013	115157,08	345636,73
1304	29.03.2013	-116,55	230387,71
1304	31.03.2013	115347,18	115230,63

Here examples with logic:

Amount For Date in 1302 agreement for 28.02.2013 equals sum of all amounts except amounts of days before.

Re: How to count that?

stantrolav — Wed, 18 Dec 2013 08:34:27 GMT

Also this column may be loaded in load script. But how can I make that?

Re: How to count that?

Anonymous — Wed, 18 Dec 2013 08:35:20 GMT

make your question more clear like

your statement

Amount For Date in 1302 agreement for 28.02.2013 equals sum of all amounts except amounts of days before.

please write or explain in diffrentt way

Regards,

anant

Re: How to count that?

stantrolav — Wed, 18 Dec 2013 08:35:43 GMT

Amount for date

is the field that i want to express.

Re: How to count that?

stantrolav — Wed, 18 Dec 2013 08:37:53 GMT

=SUM(C5:C10)

Re: How to count that?

ljackson — Wed, 18 Dec 2013 09:42:35 GMT

I think I understand what Stanislav is asking, but don't know how to write the expression requested.

The way I read it, he wants to know what the expression should be to show the 'Amount for Date' column, based on the following: Take the balance from the previous day, minus the 'Amount' for the previous day (a running balance excluding the current date)

So, taking 25.03.2013 as an example, the previous 'Amount for Date' is 100,484.17, if you minus the -105.19 you’ll get a new ‘Amount for Date’ as 100,589.36. The next entry (31.03.2013) is 100,589.36 minus 33,574.91 = 67,014.45.

Is this what you need Stanlislav?

Re: How to count that?

tresB — Wed, 18 Dec 2013 09:43:56 GMT

May be like attached sample?

Input:

Load * Inline [
Agreement,Date,Amount, "Amount for date"
1302,25.01.2013,3399907,16789757
1302,31.01.2013,-42093,1338985
1302,26.02.2013,3383526,13431943
1302,28.02.2013,-10519,10048417
1302,25.03.2013,3357491,10058936
1302,31.03.2013,-21067,6701445
1302,25.04.2013,3370058,6722512
1302,30.04.2013,-878,3352454
1302,27.05.2013,3361234,3361234
1303,26.02.2013,5676977,11320912
1303,28.02.2013,-553,5643935
1303,25.03.2013,5649465,5649465
1304,41269,11521256,46067463
1304,30.01.2013,-17466,46067463
1304,31.01.2013,11524902,34546207
1304,28.02.2013,11515708,34563673
1304,29.03.2013,-11655,23038771
1304,31.03.2013,11534718,11523063

];
outer join
Load
Agreement,
Date as NewDate
Resident Input;

Output:
Load Agreement, Date, Amount, "Amount for date",
Count(If(NewDate>=Date,Amount)) as CountF
Resident Input Group By Agreement, Date, Amount, "Amount for date";

Drop Table Input;

Re: How to count that?

stantrolav — Wed, 18 Dec 2013 10:13:18 GMT

How to count amount for date?

http://community.qlik.com/servlet/JiveServlet/download/438206-84520/comm102179.qvw

This is the answer. Thanks for cooperation.

First of all we load 3 colums from my table into table INPUT . Next:

RESULT:

LOAD

Agreement as Agreement_1,

Date,

Amount,

if(peek(Agreement_1)<>Agreement, Amount, rangesum(peek([Amount for Date]),Amount)) as [Amount for Date]

Resident INPUT order by Agreement, Date desc;