Showing Previous years and Current year data in the same view.

[former_member413809]

Hi All,

I am trying to create a Script Calculation View . On top of the view I have to create a WebI report.

I have a requirement wherein I have to show the data for all the months for the selected year (in Prompt). In the same table I have to show the data for the month of December for the previous 6 years in 6 different columns.

E.g. If I enter 2016 then monthly data for 2016 (Jan to Dec) and December’s data for 2015, 14, 13, 12, 11, 10.

The issue is when I use Variable, it filters the table for that particular year and I am unable to show the other years data.

I tried using Input Parameter/Placeholder as well; however got no luck.

Could you please help me out. I am new in this technology.

I am using the following logic:

BEGIN

var_out =

Select

"JV",

"MONTH",

"YEAR",

"FC",

"PLANT_ENGINE",

"MODEL",

"KPI",

CaseWhen"YEAR"=:Year-6 and"MONTH"='Dec'Then"Actual_Fc_Dec"End"Last_Year6",

CaseWhen"YEAR"=:Year-5 and"MONTH"='Dec'Then"Actual_Fc_Dec"End"Last_Year5",

CaseWhen"YEAR"=:Year-4 and"MONTH"='Dec'Then"Actual_Fc_Dec"End"Last_Year4",

CaseWhen"YEAR"=:Year-3 and"MONTH"='Dec'Then"Actual_Fc_Dec"End"Last_Year3",

CaseWhen"YEAR"=:Year-2 and"MONTH"='Dec'Then"Actual_Fc_Dec"End"Last_Year2",

CaseWhen"YEAR"=:Year-1 and"MONTH"='Dec'Then"Actual_Fc_Dec"End"Last_Year1",

"Actual_Fc_Jan""Jan",

"Actual_Fc_Feb""Feb",

"Actual_Fc_Mar""Mar",

"Actual_Fc_Apr""Apr",

"Actual_Fc_May""May",

"Actual_Fc_Jun""Jun",

"Actual_Fc_Jul""Jul",

"Actual_Fc_Aug""Aug",

"Actual_Fc_Sep""Sep",

"Actual_Fc_Oct""Oct",

"Actual_Fc_Nov""Nov",

"Actual_Fc_Dec""Dec",

"TARGET_CURRENT_YEAR",

"BUDGET_CURRENT_YEAR",

"DATE_OF_LOADING"

from"COPT"."COPT_DATA"

Where"YEAR"=:Year

--(PLACEHOLDER."$$YEAR$$" => :Year,

--('PLACEHOLDER'=('$$Year$$',:Year))

--('PLACEHOLDER' = ('$$Year$$', ''),

;

END

* Year is Input Parameter.
* Have commented the placeholder.
* View is validating fine

Regards,
Piyush

[former_member413809]

Hi All,

Any update?

Regards,
Piyush