Skip to Content
0

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

Jun 24, 2017 at 07:16 PM

528

avatar image

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 left characters exceeded

Hi Sanjana,

You will need to provide more information for us to help you. Here are some questions for clarification:

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.

0

Hi Mustafa,

Please find the retails.

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.

Please guide me.

Thanks,

Sanjana

0

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.

0

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

See https://archive.sap.com/discussions/thread/3520705

0

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,

ADS_SALES_QTY_VALUE.setFilterExt("0BILL_DATE", qdate3);

ADS_COLLECTION.setFilterExt("0PSTNG_DATE", qdate);




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


Please guide me.

Thanks,

Sanjana

0

Hi Mustafa,

Please find the details.

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.

sales.png

Please guide.

Thanks,

Sanjana

sales.png (2.7 kB)
0

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.

0

Hi Mustafa,

Please see my comments.

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.

Please guide me.

Thanks,

Sanjana

0
* Please Login or Register to Answer, Follow or Comment.

3 Answers

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,

Lakshmikanth Adharapurapu

bexreport-variableoffset.png

qty-comparison-last-year.png


Share
10 |10000 characters needed characters left characters exceeded
Mustafa Bensan Jun 27, 2017 at 06:11 AM
0

Hi Sanjana,

Please see my responses to your comments below:

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
10 |10000 characters needed characters left characters exceeded
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.

Please guide me for.

Thanks,

Sanjana

Show 5 Share
10 |10000 characters needed characters left characters exceeded

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

0

Hi Mustafa,

Please find the attached screenshot.

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)
0

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.

0

Hi Mustafa,

Please find the details,

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

DIS_CY.setText(ADS_TOT_DIS_COMP.getDataAsString("003N8TA82TTSXRIW6PCHMJKPF", { })); DIS_LY.setText(ADS_TOT_DIS_COMP.getDataAsString("003N8TA82TTSXRIW6PCHMJR0Z", { }));

2. Crosstab

cross-tab.jpg

Please clear my doubt.

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.

Please advice.

Thanks,

Sanjana

cross-tab.jpg (84.9 kB)
0

Sanjana,

The answers to your questions:

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.

0