0

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

Jun 24, 2017 at 07:16 PM

528

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

Hi Sanjana,

1. Is your requirement to pass the date ranges to prompt variables in the data source or to apply filters to the data source?

2. What type of data source are you using? BW, HANA or universe?

3. Your script code has hard coded dates. Why is that?

Regards,

Mustafa.

Hi Mustafa,

1. User puts the data range to see the data for that specific period. No problem in seeing the data, Now we want to compare the same selected data with the data last year.

2. We are using BW as a source.

3. Last year we have implemented the total SAP system. We have data from April'2016 till data. We have hard coded to show the data to the user only from April'2016. I will change , if you suggest. But I am new to BusinessObjects Design Studio and CSS.

Thanks,

Sanjana

sanjana singh

Hi Sanjana,

Thanks for the clarification. Here are some more questions:

1) Does your BW BEx Query have just a single prompt variable for date with a range selection, or does the query have a separate prompt variable for Start Date and End Date?

2) When you say you want to "compare qty of entered period quantity with last year same period quantity", how do you want to display this? In the same Crosstab or some other way?

Regards,

Mustafa.

Sanjana - could these inputs be provided by a BEx query, instead of placing the logic inside Design Studio?

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

Hi Mustafa,

1) I don’t have any prompt variable in BEx Query level . We have Start data and End Date prompt at the dashboard screen. After a click on OK button , the desired result is be shown.

2) We have an option Total Sales Quantity, which shows the Quantity in Box provided.

3) Now for the compare case, I have added a button COMPARE Button, After Pressing the button.This will be shown like this.

Thanks,

Sanjana

sales.png (2.7 kB)
sanjana singh

Hi Sanjana,

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.

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.

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

Lakshmikanth Adharapurapu Jun 27, 2017 at 02:17 PM
0

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

Share
Mustafa Bensan Jun 27, 2017 at 06:11 AM
0

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.

cy.png (67.6 kB)
py.png (67.9 kB)
Share
sanjana singh Jun 30, 2017 at 11:29 AM
0

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.

Thanks,

Sanjana

Show 5 Share

Can you provide a screenshot of the BEx variable input and the resulting output in BEx Analyser?

Mustafa Bensan

Hi Mustafa,

I am getting desired result in BEx. But in design studio, I am getting only the Current Year values in both the field ( CY Field and LY Field).

Thanks,

Sanjana

input-variable.jpg (152.1 kB)
output.jpg (134.3 kB)
sanjana singh

Hi Sanjana,

To help track down the issue, can you do the following?

1. Post the script code you are using in Design Studio;

2. Assign the BEx Query data source to a Crosstab component, execute the application and provide a screenshot of the Crosstab showing the CY and LY columns;

3. Provide a screenshot of your complete application.

Regards,

Mustafa.

Hi Mustafa,

1.script code ( Might be wrong )

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 lday1 = '01'; // COMP

var lmonth1 = '04'; // COMP

var lyear1 = '2016'; // COMP

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

2. Crosstab

cross-tab.jpg

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.

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

Thanks,

Sanjana

cross-tab.jpg (84.9 kB)
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.