Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

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


robert fuschetto

Recommended Posts

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:

image.png.bd5de887c7c9375df4c4db002132d58c.png 

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.

Link to comment
Share on other sites

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');
Link to comment
Share on other sites

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');

image.png.6c1ae3ccdca38879dd9f41af1541eea0.png 

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....

Link to comment
Share on other sites

&&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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

image.png.993b9f47b48edd80e135fcf473100a9e.png 

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:

image.png.c7fb2929e849d263df235ffb1c2bd0f3.png 

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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
Link to comment
Share on other sites

  • 3 weeks later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...