**CLOSED Trying to get Days and Work Days in month from Variable or using &DATE

[robert fuschetto]

We have a report. The page header says Actual Dollars By Dept thru Yesterday. It also shows &DATE on there. No very sophisticated! I'd Like it to say 'Actual Dollars by Dept thru MM/DD/YYYY' then below that to say 'Total elapsed days: #, Total Elapsed Work Days #'.

&DATE gets me the date and see various flavors of it under SYSTEM VARIABLES:

 

Is there a way to get the Prior Day here..since the report is through yesterday. Can I get the Days in Month or Work Days here? Or must all this be done with a DEFINE? We are using App Studio 8207v28.

[Patrick Huebgen]

You can create your own System Variables - here are some samples I use - sorry some names are German Names fo should be self explaining.

You can put them into your fex or better - to have them active all the time just put them into a universal profile (open following link and search for universal profile)

https://docs.tibco.com/webfocus/8207/doc/html/index.jsp?topic=%2Fcom.ibi.help.admin%2Fsource%2Fadmin_console22.htm

-SET &&HEUTE= EDIT(&DMYY , '99.99.9999'); -SET &&JETZT= EDIT(&TOD , '99$.99$.99'); -SET &&MONAT_3 = DATECVT(DATEADD(DATECVT(&YYMD, 'I8YYMD', 'YYMD'), 'M' , '-3'), 'YYMD', 'A8YYMD'); -SET &&YESTERDAY = DATECVT(DATEADD(DATECVT(&YYMD, 'I8YYMD', 'YYMD'), 'D' , '-1'), 'YYMD', 'A8YYMD'); -SET &&YESTERDAY_WD = DATECVT(DATEADD(DATECVT(&YYMD, 'I8YYMD', 'YYMD'), 'BD' , '-1'), 'YYMD', 'A8YYMD'); -SET &&FROM_DATE_MO_3 = CHGDAT('YYMD', 'MDYYX', &&MONAT_3, 'A17'); -SET &&HEUTE = DATECVT(DATECVT(&YYMD, 'I8YYMD', 'YYMD'), 'YYMD', 'A8YYMD'); -SET &&TO_DATE_HEUTE = CHGDAT('YYMD', 'MDYYX', &&HEUTE, 'A17');

[robert fuschetto]

Very Interesting:

-SET &&HEUTE= EDIT(&DMYY , '99.99.9999');

-SET &&JETZT= EDIT(&TOD , '99$.99$.99');

-SET &&MONAT_3 = DATECVT(DATEADD(DATECVT(&YYMD, 'I8YYMD', 'YYMD'), 'M' , '-3'), 'YYMD', 'A8YYMD');

-SET &&YESTERDAY = DATECVT(DATEADD(DATECVT(&YYMD, 'I8YYMD', 'YYMD'), 'D' , '-1'), 'YYMD', 'A8YYMD');

-SET &&YESTERDAY_WD = DATECVT(DATEADD(DATECVT(&YYMD, 'I8YYMD', 'YYMD'), 'BD' , '-1'), 'YYMD', 'A8YYMD');

-SET &&FROM_DATE_MO_3 = CHGDAT('YYMD', 'MDYYX', &&MONAT_3, 'A17');

-SET &&HEUTE2 = DATECVT(DATECVT(&YYMD, 'I8YYMD', 'YYMD'), 'YYMD', 'A8YYMD');

 

I am not sure I follow:

-SET &&FROM_DATE_MO_3 = CHGDAT('YYMD', 'MDYYX', &&MONAT_3, 'A17');

Is it somehow returning the date of the last work day?

I have to find this in the documentation. I still need Number of Work Days so far in the month....

[Patrick Huebgen]

&&YESTERDAY_WD is the last workday - if you need a different display format please see the other samples how to change format.

number of workday in month

To calculate the difference of days - you can use DATEDIFF

https://infocenter.informationbuilders.com/wf80/index.jsp?topic=%2Fpubdocs%2Freporting%2FUsingFunctions%2Fsource%2Fdatetime10.htm

I added my samples so you have some code samples to copy from. The structure is always the same just different function with different number of Arguments

[David Beagan]

Here is some code that gives you the work days, before today, in the current month:

-SET &Start_Date = &DATEH6 | '01';-SET &End_Date = &YYMD; -SET &Work_Days = DATEDIF( DATECVT(&Start_Date,'I8YYMD','YYMD') , DATECVT(&End_Date,'I8YYMD','YYMD') , 'BD' ); -TYPE Start_Date = &Start_Date-TYPE End_Date = &End_Date -TYPE Work_Days = &Work_Days

I think the DATEDIF function counts the start date but not the end date.

The 'BD' parameter of the DATEDIF indicates that the counting of days is subject to the setting of the BUDSDAYS. By default it is set to

BUSDAYS = _MTWTF_

which counts each of the days Mon-Fri but not Sat or Sun.

[robert fuschetto]

THANKS ALL!!!!

Very Helpful. Now I am just trying to better format the date; ie MM/DD/YYYY...no doubt it's in the documentation somewhere.

[Patrick Huebgen]

What is the format you are looking for?

Just share what you have now as a focus code and the desired output please

[robert fuschetto]

Sure:

 

I'd like the 10092023 to be formatted as 10/09/2023.

Thanks!

-SET &&YESTERDAY2 = DATECVT(DATEADD(DATECVT(&YYMD, 'I8YYMD', 'YYMD'), 'D' , '-1'), 'YYMD', 'A8MDYY');

[David Beagan]

-SET &Date1 = '10092023'; -SET &Date2 = EDIT(&Date1,'99/99/9999'); -TYPE Date2 = &Date2

 The EDIT function is very useful.

Documentation: EDIT: Extracting or Adding Characters

[robert fuschetto]

AWESOME. Ok, one last one...I noticed something in APP Studio I never took notice of before:

 

What I would like to do is have a flag that tells me if I am on Day 1 of the Month; likely I can use a SET command using code shown above in some fashion....?. Call it DAYONEFLAG

What I want to do is if DAYONEFLAG is True then call a particular WHERE statement otherwise call another. I seem to recall using a GOTO years ago but I am wondering if I can somehow use this:

 

In tinkering it seems to add an IF statement just before my where...I am unsure how to complete the IF...do I allow my IF TRUE WHERE to follow the IF then put an: ELSE then my FALSE IF WHERE etc....

Thoughts....either way thanks for the assistance. This is great!

[Patrick Huebgen]

IF is just an older version of WHERE no IF THEN ELSE

You rather need a DEFINE field that ontains only the day of the month and build a Filter like this (WHERE DAY EQ 1 AND FILTER1) OR (WHERE DAY NE 1 AND FILTER2)

[robert fuschetto]

I got it..Thanks again.

[Patrick Huebgen]

Have a great day!

[David Beagan]

The IF you see in App Studio is an older incarnation of WHERE.

Maybe you thinking of the Dialogue Manager command:

-IF

which might be workable for your situation.

However, it seems like you could just do it all with WHERE. For example, with the ggsales sample file we can filter for 'West' on the first of the month, filter for 'Southwest' for the other days.

SET TESTDATE='20231001'-RUN  TABLE FILE GGSALES "Run Date: &DATEMtrBDBYY " SUM UNITS BY REGION BY CITY BY ST WHERE ( ('&DATED' EQ '01') AND (REGION EQ 'West') ) OR ( ('&DATED' NE '01') AND (REGION EQ 'Midwest') ) END

A couple of things to note.

The &DATED variable gives today's day of the month.

The TESTDATE setting temporarily uses the specified date for the system date — useful for testing differet date scenarios.

[robert fuschetto]

I was just wondering how I could test...rather than waiting till 11/1. Thanks! The code is also a lot less verbose than the DEFINE I had created...and apparently do not need:

-*DEFINE FILE TBLTEMP_ADHOC_PATRICEINTITALMONTHLYSTATS

-*DAYONEFLAG/A5=IF (DATEDIF( DATECVT(&Start_Date,'I8YYMD','YYMD') , DATECVT(&End_Date,'I8YYMD','YYMD'), 'D' )) +1 EQ 1 THEN 'TRUE' ELSE 'FALSE';

-*END

Thanks again.

[Patrick Huebgen]

The downside of the variable usage is that in some cases this is not pushed down to the database sql statement - please check the run-time and or generated statement of your report.

Had this effect in the past - but not all the time - depends on the database.

[David Beagan]

Patrick, what was passed to the database, the variable name? Was it a system variable or one of your own?

Did you try putting .EVAL on the variable?

[Patrick Huebgen]

I guess in this case Robert should be safe - most time this was related to Variables within functions that were not passed down to the DB - but I'm always double checking to make sure I'm not creating extra database load

[David Beagan]

Yes, always good to double check the SQL that is getting passed to the database.

Trace Statements for SQL

[robert fuschetto]

Hey Dave, this all works. I have Work_days as such:

-SET &Work_Days = DATEDIF( DATECVT(&Start_Date,'I8YYMD','YYMD') , DATECVT(&End_Date,'I8YYMD','YYMD') , 'BD' );

and in my Page Header:

HEADING

"Actual MTD Units Vs Monthly Budget as of: &&YESTERDAY3"

"FYI: &Days completed days in the month of which &Work_Days were business days"

It's all working as desired.

Question:

Can I use &Work_Days in a DEFINE? I'd like to create a new -SET statement that tells me the number of Work Days (I'd think that could be done) in the month (WDinMonth). Then DEFINE PCTofMTH = &WorkdDays / &WDinMonth. Now I know how far through the month I am as a percentage. I'd like to then multiple my MonthlyBudget buy this value to apportion it on the report for the month.

Can I use the SET &'s in these DEFINE Calculations?

[David Beagan]

You could use the &s in a DEFINE, but you don't need to. You can accomplish it with Dialogue Manager -SET statements:

-SET &Start_Date = &DATEH6 | '01';-SET &End_Date = &YYMD;-SET &NextMon = AYM(&DATEH6, +1, 'I6YYM') | '01';-SET &Work_Days = DATEDIF( DATECVT(&Start_Date,'I8YYMD','YYMD') , DATECVT(&End_Date,'I8YYMD','YYMD') , 'BD' );-SET &WDinMonth = DATEDIF( DATECVT(&Start_Date,'I8YYMD','YYMD') , DATECVT(&NextMon,'I8YYMD','YYMD') , 'BD' );-SET &PCTofMTH = &Work_Days / &WDinMonth * 100; -TYPE Start_Date = &Start_Date-TYPE End_Date = &End_Date-TYPE NextMon = &NextMon-TYPE Work_Days = &Work_Days-TYPE WDinMonth = &WDinMonth-TYPE PCTofMTH = &PCTofMTH

[robert fuschetto]

One last one and I'll close this topic! Can I write code to obtain the MAX transaction date from my table and store it in a variable..almost like I'd like so _SET &MaxTxDate = the results of the query.

[Patrick Huebgen]

-SET &TIME_DATE='';

TABLE FILE retail_samples/wf_retail

SUM MAX.WF_RETAIL.WF_RETAIL_TIME_SALES.TIME_DATE

ON TABLE HOLD AS DATEMAX FORMAT ALPHA

END

-RUN

-READFILE DATEMAX

-TYPE &TIME_DATE

[robert fuschetto]

HOW DO I GET YESTERDAY's DATE IN AN INFOASSIT CHART?

[Patrick Huebgen]

Where and how do you want to use it????