### Last year Comparison based on Current year Selection in Design Studio.

Dear Expert,

i need to compare qty of entered period quantity with last year same period quantity.

I am new to design studio.

Here the user inputs the data e.g. 20.06.2017 to 24.06.2017 and the result would show the current 20.06.2017 to 24.06.2017 as well as 20.06.2016 to 24.06.2016.

Please find the code for the date selection .

And please guide, so that I can achieve.

var date = START_DATE.getDate();

var day = Convert.subString(date, 6,8);

var month = Convert.subString(date, 4,6);

var year = Convert.subString(date, 0,4);

var qdate0 = month + '-' + day + '-' + year;

if ((date == '') ) {

qdate0 = date;

}

else {

qdate0 = month + '-' + day + '-' + year;

}

var date1 = END_DATE.getDate();

var day1 = Convert.subString(date1, 6,8);

var month1 = Convert.subString(date1, 4,6);

var year1 = Convert.subString(date1, 0,4);

var qdate1 = month1 + '-' + day1 + '-' + year1;

if ((date1 == '') ) {

qdate1 = date1;

}

else {

qdate1 = month1 + '-' + day1 + '-' + year1;

}

var qdate = qdate0 + ' ' + '-' + ' ' + qdate1;

if ((date == '') || (date1 == '')) {

qdate = date1 ;

}

else {

qdate = qdate0 + ' ' + '-' + ' ' + qdate1;

}

var day2 = '01';

var month2 = '04';

var year2 = '2017';

var qdate2 = month2 + '-' + day2 + '-' + year2;

var qdate3 = qdate2 + ' ' + '-' + ' ' + qdate1;

var lday1 = '01';

var lmonth1 = '04';

var lyear1 = '2016';

var ldate = lmonth1 + '-' + lday1 + '-' + lyear1;

Thanks,

Sanjana

10|10000 characters needed characters exceeded
• sanjana singh Tammy Powlas

Hi Tammy,

We have Date selection input at the Dashboard level, Don't have at the BEx query level.

And at the back-end we have codes like this,

I have mentioned the entire codes at the starting of this thread.

Thank you for the reference thread. But my problem is , if user enters the date range 20.05.2017 to 30.05.2017 and the result would show for the current selection 20.05.2017 to 30.05.2017 as well as 20.05.2016 to 30.05.2016 (Last Year ).

Thanks,

Sanjana

• Mustafa Bensan sanjana singh

Hi Sanjana,

See my comments below:

1) "I donâ€™t have any prompt variable in BEx Query level". If you don't have any prompt variables at the BEx Query level, how are you restricting the data to the date range input by the user? Are you using the setFilter() script command?

2) Based on your screenshot for the comparison output, as Tammy has suggested, the best approach would be to create a BEx Query with prompt variables for the start and end dates and then based on these, two quantity measures, once for current period and the other for prior period.

Regards,

Mustafa.

• sanjana singh Mustafa Bensan

Hi Mustafa,

1) Yes, we are using setFilterExt () script command.

2) I have checked the thread suggested by Tammy. The thread show the month wise data with Last year month wise comparison.

Please clear my doubt,

A) If I create a BEx Query with prompt variables for the start and end dates and then based on these, two quantity measures. Do I need to create any customer exit variable for the last year value.

B) If I create the prompt variables at BEx level, which command shall I use in Design studio.

Thanks,

Sanjana

• Jun 27, 2017 at 02:17 PM

Hi Sanjana,

I completely agree with Mr Mustafa statements.

Step1.You can create one restricted keyfigure based on quantity and based on 0calday, you can do a offset with 365, from my screen shot you see it for 0calmonth because i do not have 0calday in my query so just for eg i have used calmonth/year.

Step2.You can use charectristics Qty, QtyLY(quantity last year) and display the data as shown in the second screen shot.

BR,

bexreport-variableoffset.png

qty-comparison-last-year.png

10|10000 characters needed characters exceeded
• Jun 27, 2017 at 06:11 AM

Hi Sanjana,

A) If I create a BEx Query with prompt variables for the start and end dates and then based on these, two quantity measures. Do I need to create any customer exit variable for the last year value.

You should be able to create a single date range variable and then offset by 365 days as shown below:

B) If I create the prompt variables at BEx level, which command shall I use in Design studio.

You can either trigger the prompt dialogue with DataSource.openPromptDialog() or use DataSource.setVariableValue() based on the input values from the Date input components.

Regards,

Mustafa.

10|10000 characters needed characters exceeded
• Jun 30, 2017 at 11:29 AM

Hi Mustafa,

I have created the same in BEx query level. This is running compleately fine in BEx.

I have some date coding in Design Studio , but the same is not working for Compare button.

By default it is showing the total data.

Please guide me for.

Thanks,

Sanjana

10|10000 characters needed characters exceeded
• Mustafa Bensan sanjana singh

Sanjana,

1. Can we use the some script to minus the year

eg. 20.06.2017 to 24.06.2017 - Selection

and 20.06.2016 to 24.06.2016. output will read the table.

You SHOULD NOT calculate prior year in scripting because this is already done in the BEx Variable.

2. Can we use Cell calculation in Design studio in this type of issue.

No, you cannot.

The problem with your script code is that you are not executing a setVariableValueExt() command based on the user selected date range.

Regards,

Mustafa.