setting Date from another Date data-row

[#1]

I have a Calendar Table with calendar and Fiscal Dates. I need to have a Document OnLoad Trigger which sets a Default Date, a Defaul Accounting Period (Fisca Month) and Default Fiscal Year.For the Default Date I need to set the Calendar Date from a Load-Date minus 3 Days (only 1 Row), i try this but with no success:

=ONLY({<DATE.DAY_DT={$(DATE(LOAD_DATE.DAY_DT-3)) }>} DATE.DAY_DT)

And for setting the Fiscal Month (AP), I have just a vague idea:

=ONLY({<DATE.DAY_DT={$(DATE(LOAD_DATE.DAY_DT-3)) }>} DATE.AP)

Any ideas?

[Miguel_Angel_Baeyens]

Hi,

Try changin the syntax as below

=ONLY({< DATE.DAY_DT = {'$(=DATE(LOAD_DATE.DAY_DT -3))'} >} DATE.DAY_DT)

Note the quoting and the "=" before the Date() function in the set modifier. Note as well that Only() does return null if there is actually more than one possible value for the expression within, and not one value. Min() or MinString() instead of Only() should do.

Hope that helps.

Miguel

[#3]

Hello MiguelIt works, but only if I use it without minus 3 days. This probably because I have onbly one date-row in LOAD_DATE. What other way could i do to get the DATE.DAY_DT minus 3 days from the LOAD_DATE-DAY_DT?

[Miguel_Angel_Baeyens]

Hi,

You can use instead the function ReloadTime() that returns a complete timestamp with the last reload time and date.

Hope that helps.

Miguel

[#5]

The Date is data from a ETL-Load and I need to have a date minus 3 Days of the LOAD_DATE.DAY_DT

[Miguel_Angel_Baeyens]

Hi,

I'm afraid I'm issing something since I don't have your model in front of me. You can save the highest value (or the only value) of LOAD_DATE.DAY_DT in a variable, then use it, or does it come from a different file? In this case you can save the value of LOAD_DATE.DAY_DT in a QVD file then LOAD in the file where you want to get that date minus 3.

Hope that makes some sense.

Miguel

[swuehl]

uweh,

is your LOAD_DATE.DAY_DT a time stamp? Then you probably need to apply an additional daystart() function to get rid of the time fraction part before using it for the set element for DATE.DAY_DT.

I assume your calendar shows the complete range of dates in question.

Regards,

Stefan

[#8]

This would work perfekt:

=ONLY({< DATE.DAY_DT = {'$(=(LOAD_DATE.DAY_DT))'}>} DATE.DAY_DT )

B ut I have the date  it not 3 days back

DAY_DT is of type DATE

[swuehl]

Since it's all about just dates, no confidential data involved, would it be possible that you post a small sample app here to the forum?

[Miguel_Angel_Baeyens]

Hi,

So assuming LOAD_DATE.DAY_DT is created in a different file (so when you load the other file, LOAD_DATE.DAY_DT is empty), why don't you save it into a QVD file that can be easily read in the latter file so you always have the value to substract 3? Or do you mean you only have in this file one possible date for DATE.DAY_DT? Can you please elaborate on your model and number of documents involved?

Regards.

Miguel