Minimum Date calculation

[AH]

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

[rubenmarin]

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]

[Anonymous]

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

[AH]

Zhihong

FInd the load statement.

[AH]

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

[rubenmarin]

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.

[sasikanth · Accepted solution]

try like below

aggr(min(Installdt),Customer_ID)

as a calculated Dim in chart

[AH]

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.

[rubenmarin]

Yes, as sasikanth said, you can use

aggr(min(Installdt),Customer_ID)

[AH]

Thank you guys! Its working great!

I am so happy to be in this community!

Thanks,

Shan