topic Re: Minimum Date calculation in QlikView

Minimum Date calculation

AH — Mon, 24 Nov 2014 14:23:06 GMT

Hi,

I have couple of dates for a single customer. So I want to calculate the first date as Account start date. I was trying to use this in the script but its showing invalid expression.

Min((Date#(InstallDt,'YYYYMMDD')) as [Account Start Date])

Am I doing wrong somewhere?

Thanks,

Shan

Re: Minimum Date calculation

rubenmarin — Mon, 24 Nov 2014 14:27:13 GMT

Hi, try removing 1 '(' after Min():

Min(Date#(InstallDt,'YYYYMMDD')) as [Account Start Date])

Re: Minimum Date calculation

anbu1984 — Mon, 24 Nov 2014 14:30:03 GMT

Also remove last ')'

Min(Date#(InstallDt,'YYYYMMDD')) as [Account Start Date]

Re: Minimum Date calculation

AH — Mon, 24 Nov 2014 14:46:26 GMT

Still Showing Invalid Expression.

Thanks,

Shan

Re: Minimum Date calculation

rubenmarin — Mon, 24 Nov 2014 14:50:02 GMT

Hi Shan,

For [Account Start Date] you are using Min, to do this you need to have a Group By clause?

In case you have the Group By clause yo need to add all non-aggregated fields to the group by, or use funcions like MinString(), Min(), Sum() to convert non-aggregated fields into aggregated fields.

Hope this helps

Re: Minimum Date calculation

Mon, 24 Nov 2014 14:54:07 GMT

Hi,

Try this

Min(Date(InstallDt,'YYYYMMDD')) as [Account Start Date]

or Min(Date#(InstallDt,'YYYYMMDD')) as [Account Start Date]

Re: Minimum Date calculation

AH — Mon, 24 Nov 2014 15:00:58 GMT

The expression is not working.

Re: Minimum Date calculation

AH — Mon, 24 Nov 2014 15:03:02 GMT

Ruben,

No I am not using any Goup By Clause.

Thanks,

Shan

Re: Minimum Date calculation

sasikanth — Mon, 24 Nov 2014 15:05:50 GMT

try some thing like

date#(min(Installdt),'YYYYMMDD') as [Account start date]

Re: Minimum Date calculation

AH — Mon, 24 Nov 2014 15:06:07 GMT

Actually i am using this Minimum Date (Account Start date) in the Chart. Is there any way i can change the dimension and make the expression there to get the Minimum date?

Thanks,

Shan

Re: Minimum Date calculation

rubenmarin — Mon, 24 Nov 2014 15:06:30 GMT

Without a Group By clause each record has only one value for InstallDt, so you can remove the Min() clause:

Date(Date#(InstallDt,'YYYYMMDD')) as [Account Start Date]

Re: Minimum Date calculation

Anonymous — Mon, 24 Nov 2014 15:07:00 GMT

Hi, are you sure it is caused by the min() problem, maybe some others. Can you provide the whole LOAD expression?

Re: Minimum Date calculation

AH — Mon, 24 Nov 2014 15:13:34 GMT

Zhihong

FInd the load statement.

Re: Minimum Date calculation

AH — Mon, 24 Nov 2014 15:16:03 GMT

Ruben,

You are right! Only one value for InstallDt. Ok, my problem is, One cusstomer has many installation date of instruments. So, I wanna pick the first installation date and declare that as the account start date.

Thanks,

Shan

Re: Minimum Date calculation

rubenmarin — Mon, 24 Nov 2014 15:22:51 GMT

You can first load your table without [account start date], then do a left join like:

Left Join

LOAD SourceCustId,

Min(Date#(InstallDt,'YYYYMMDD')) as [Account Start Date]

FROM table Group By SourceCustId

Change SourceCustId for the field that will link these tables if it's not SourceCustId.

Re: Minimum Date calculation

sasikanth — Mon, 24 Nov 2014 15:23:47 GMT

try like below

aggr(min(Installdt),Customer_ID)

as a calculated Dim in chart

Re: Minimum Date calculation

AH — Mon, 24 Nov 2014 15:40:06 GMT

Is there anyway i can achieve that in The UI side? I am using the [account start date] in a chart and date is in the dimension not in the expression.

Re: Minimum Date calculation

rubenmarin — Mon, 24 Nov 2014 15:44:15 GMT

Yes, as sasikanth said, you can use

aggr(min(Installdt),Customer_ID)

Re: Minimum Date calculation

AH — Mon, 24 Nov 2014 18:38:35 GMT

Thank you guys! Its working great!

I am so happy to be in this community!

Thanks,

Shan