cancel
Showing results for 
Search instead for 
Did you mean: 

Date calculation with system date minus 45 days

Former Member
0 Kudos

Hello Experts,

  We have a requirement where we need to default date range to bex query filter by calculating from date as system date minus 45 days and to date as system date for example "20140215 - 20140330".  How can we achieve this in Design Studio 1.2 SP01.  I know we can do this using BEx variables, but I am curious by using Java Script if I can calculate dates and pass to BEx Query.  Thanks in advance.

Thanks

Mohan Kolli

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi,

First of all Try To bring Last 45 Days Data from Source System ( SAP BI BEx Query ) .

Its Easy & Fast .

But If for some reason you are not able to do it then Please Try The code below.

You May need to modify it .

var vDate = APPLICATION.getInfo().dateNowInternalFormat;

var vDay = Convert.subString(APPLICATION.getInfo().dateNowInternalFormat, 6,8);

var vMonth = Convert.subString(APPLICATION.getInfo().dateNowInternalFormat, 4,6);

var vYear = Convert.subString(APPLICATION.getInfo().dateNowInternalFormat, 0,4);

var vLastMonth = Convert.stringToInt(vMonth)-1;

var vLasttoLastMonth = Convert.stringToInt(vMonth)-2;

TEXT_13.setText(vDate);

TEXT_14.setText(vDay);

TEXT_15.setText(vMonth);

TEXT_16.setText(vYear);

var vPassday = Convert.stringToFloat(vDay) - 45.00;

var vPassyear = "";

var vPassmonth = "";

// Year Passing

if ((Convert.stringToInt(vMonth) <= 2) && (Convert.stringToInt(vDay) < 15)) {

    vPassyear = Convert.floatToString((Convert.stringToFloat(vYear)-

    1),"###0");

    TEXT_17.setText(vPassyear);

}

else {

    vPassyear = Convert.floatToString((Convert.stringToFloat(vYear)),"###0");

    TEXT_17.setText(vPassyear);

}

// Month Passing

if ((Convert.stringToInt(vMonth)>= 2) && (Convert.stringToInt(vDay)>= 15)) {

    vPassmonth = Convert.floatToString((Convert.stringToFloat(vMonth)- 1),

    "#0");

    TEXT_18.setText(vPassmonth);

}

else {

    if ((Convert.stringToInt(vMonth)== 1) && (Convert.stringToInt(vDay)>= 15)) {

        vPassmonth = "12";

        TEXT_18.setText(vPassmonth);

    }

    else {

        if ((Convert.stringToInt(vMonth)>= 3) && (Convert.stringToInt(vDay)< 15)) {

            vPassmonth = Convert.floatToString((Convert.stringToFloat(vMonth)- 2),

            "#0");

            TEXT_18.setText(vPassmonth);

        }

        else {

            if ((Convert.stringToInt(vMonth)== 2) && (Convert.stringToInt(vDay) < 15))

            {

                vPassmonth = "12";  

                TEXT_18.setText(vPassmonth);          

            }

            if ((Convert.stringToInt(vMonth)== 1) && (Convert.stringToInt(vDay) < 15) ) {

                vPassmonth = "11";

                TEXT_18.setText(vPassmonth);

            }

        }

    }

}

// Day Passing First Negative State

if (vPassday <= 0) {

    if (((vLastMonth == 1) || (vLastMonth == 03) || (vLastMonth == 05) ||

    (vLastMonth == 07) || (vLastMonth == 08) || (vLastMonth == 10) || (vLastMonth

    == 12))) //

{

        vPassday = vPassday + 31.00;

//        TEXT_18.setText(vPassday + "");

    }

    else {

        if (((vLastMonth == 4) || (vLastMonth == 06) || (vLastMonth == 09) ||

        (vLastMonth == 07) || (vLastMonth == 11))) {

            vPassday = vPassday + 30.00;

//            TEXT_18.setText(vPassday + "");

        }

        else {

            if ((vLastMonth == 2)) {

                vPassday = vPassday + 28.00;

//                TEXT_18.setText(vPassday + "");

            }

        }

    }

}

// Day Passing Second Negative State

if (vPassday <= 0) {

    if (((vLasttoLastMonth == 1) || (vLasttoLastMonth == 03) || (vLasttoLastMonth == 05) ||

    (vLasttoLastMonth == 07) || (vLasttoLastMonth == 08) || (vLasttoLastMonth == 10) || (vLasttoLastMonth

    == 12))) //

{

        vPassday = vPassday + 31.00;

//        TEXT_18.setText(vPassday + "");

    }

    else {

        if (((vLasttoLastMonth == 4) || (vLasttoLastMonth == 06) || (vLasttoLastMonth == 09) ||

        (vLasttoLastMonth == 07) || (vLasttoLastMonth == 11))) {

            vPassday = vPassday + 30.00;

//            TEXT_18.setText(vPassday + "");

        }

        else {

            if ((vLasttoLastMonth == 2)) {

                vPassday = vPassday + 28.00;

//                TEXT_18.setText(vPassday + "");

            }

        }

    }

}

TEXT_19.setText(vPassday+"");

Try It & Let mw know .

Regards ,

Nikhil

former_member262292
Discoverer
0 Kudos

Just saw another thread on this:

former_member262292
Discoverer
0 Kudos

Hi Mohan,

Design Studio lets you read the system date (not sure if this is the SAP system date or taken from the browser):

APPLICATION.getInfo().dateNowInternalFormat

From that you can get month and year:

var year = Convert.subString(APPLICATION.getInfo().dateNowInternalFormat, 0, 4);

var month = Convert.subString(APPLICATION.getInfo().dateNowInternalFormat, 4, 6);

Since both are strings, you need to convert them to integers to be able to add/substract:

var monthint = Convert.stringToInt(month);

You'll need to handle month and year changes (e.g. if the date 45 days ago is 1 or 2 months ago) by some If-Then logic.

Hope this helps.