ASK.. how to LOAD MIN(DATE) in qlikview??

[#1]

hello there, need help plis 😄

really need your help, this is urgent, plis help.. thank you before, i really apreciate it.

plis read and see the picture clearly, and you will understand what i mean..

i want to make a financial repot about agingday, duedate, etc

my case is something related with buying credits.

how can i load min(date) in qlikview??

this is my condtion,

i have a field named "CorAccountDetail" , which ID (NoRek+Nopin) is 4007014852-001, and have 13 record, because customer buying something and credit in 13 times.

see the picture below (IN SQL)

this is my QV load screen,,

note , I load it from my .qvd file.

PICTURE ABOVE CAN RUN, and IT'S WORK, Succes load !!

BUT

if i type,

num(interval(today() - date(min(CicilDate))) as AgingDay or

num(interval(today() - min(date(CicilDate))) as AgingDay or

min(CicilDate) as MinCicildate or etc all doen't work, it occured error

this is my qv table ***TABLE 1

in two chart above, (just said chart A(AgingDay) and chart B(1-30))

339.000 divided into all the period from 1-30, 31-60.... , 90 - 150. because norek+nopin 4007014852-001 have more than 1 CicilDate,

so calculate in expression will result in more than 1 agingday. which suit each period in table B.

what i want is all the debt will shown up in field 90-150 which total debt is 1.596.000 because norek+nopin 4007014852-001 maximum aging day is 105.

to do that i must get min(cicildate), so 1 norek+nopin only has 1 cicildate.

and this is the expression in each period(just change the period in AgingPeriod)

and in table i can get min(date) using this expression, but in load script it doesn't work, because of that, my financial report have an eror.

the problem is,

see TABLE 1, the agingday there is 105, so sum(angsuran)which is 1.596.000 must be in column 91-150, but in that chart it divided into all period.i know the reason, because my expression calculated all the cicilDate in that NoRek.CorAccountDetail. (there is 4 cicildate where paidstatus = 0, so it calculated all the cicildate and sum(angsuran) divided into each period).

so i just only need the min(cicildate) and sum(angsuran) placed in "period of min(cicildate)"

when i type min(CicilDate) in load script, it occured eror.

my question is,

how to load min(CicilDate) into qlikview??

so i can load my AgingDay correctly,

Num(Interval(today() - min(date(CicilDate))) as AgingDay. <----- this doesn't work

now, in CorAccountDetail, one (NoRek+NoPin) have more than one cicil date (see the TABLE 1).

sooo i just want to get, one (NoRek+NoPin) only have one cicildate (WHICH IS MIN(CICILDATE))

thank you for the help,

sorry my english is poor =P

Regards !!

[#2]

can anybody help..

thank you 😄

[#3]

Can you please summarize your question.

[#4]

Load statement works only if the data(Table) is already loaded in to the qlikview memory.So kindly check whether you are using the load statement before the actual table is loaded or is it after the table is loaded.

Can you please scramble the data and attach it here so that it will be easy for others to look in to the issue.

Steps to scramble the data:

Settings --> Properties --> Scrambling

[#5]

hello Santosh,

my question is,

how to load min(CicilDate) into qlikview??

so i can load my AgingDay correctly,

Num(Interval(today() - min(date(CicilDate))) as AgingDay. <----- this doesn't work

now, in CorAccountDetail, one (NoRek+NoPin) have more than one cicil date (see the TABLE 1).

sooo i just want to get, one (NoRek+NoPin) only have one cicildate (WHICH IS MIN(CICILDATE)).

Hello Rikab,

note that, i load using .qvd file,

i have already load it from sql and save to qvd file, and i load again from qvd file.

😄

[#6]

Hi ,

Can you tell me why don't u use

today() - date(CicilDate) as AgingDay, for calculating aging., becasue if u use today() - min(date(CicilDate)) inside script then it will have the same result unless u don't make a group by.

As per my understanding you need one agingDay for every date record of the table.

Please try.

[#7]

hmm, maybe the logic is like this,

note* my case is something related with, buying Credit.

let's assume,

in table [CorAccountDetail], Field "ID" 500000-001 have 12 records (because it buying something, and credit in 1 year)

field = CicilDate(same as Duedate), Payment, Paidstatus.

Record 1 2010-01-01 , 100000 , Already Paid.

Record 2 2010-02-01 , 100000 , Already Paid

Record 3 2010-03-01 , 100000 , Haven't Paid

Record 4 2010-04-01 , 100000 , Haven't Paid.

Record 5 2010-05-01 , 100000 , Haven't Paid.

Record 6 2009-06-01 , 100000 , Haven't Paid.

Record 7 2009-07-01 , 100000 , Haven't Paid.

Record ETC.....

and agingday period is

1 month(as period1),

2 month(as period2),

3 month(as period3),

4 month(as period4),

5 month(as period5),

6 month(as period6),

if i load only today() - date(cicildate), i it calculate all the record right?

and 1 ID have more than 1 cicildate, and 1 ID have more than 1 agingday.

which is in ID 500000- 001 have

record 3 agingday = 5 month,

record 4 agingday = 4 month,

record 5 agingday = 3 month,

record 6 agingday = 2 month,

and etc

and in qv table show will be (see table picture number 2 in my first post)

ID period1 period2 period3 period4 period5 period6

50000-001 100000 100000 100000 100000 100000 100000

because 500000-001 have more than 1 agingday in each period.

but what i want is (because is haven't paid for 6 month) so the agingday in 50000-001 is only 6 month and sum off the debt will all in period6 which is 6 month

ID period1 period2 period3 period4 period5 period6

50000-001 - - - - - 600000

to do that, i must get min(cicildate) in 50000-001

got it?? 😄

thank you Santosh

[#8]

can u provide me the error message when u are using min in your script.

[#9]

[#10]

this is the eror script