Set Analysis to filter the data based on value from standalone inline table
I have a fact table which contains
[Vehicle Summary]:
DATE(TRIP_DAY) as [Trip Day], TRIP_MONTH as [Trip Month], LEFT(TRIP_MONTH,3)&','&TRIP_YEAR as [Trip Year Month Text], TRIP_YEAR as [Trip Year], TRIP_YEAR_MONTH as [Trip Year Month], TRIP_FISCAL_PERIOD as [Trip Fiscal Period],
TRAVELLED_KM AS [Distance Travelled per day(kms)]
The users want to filter the fact data by
Current Year, Last Year, Current Month, Last Month, Current Financial Year, Last Financial Year, Last 3 Months, Last 6 Months
So I created 8 variables in the app
Set vCurrentYear = extract(year from sysdate); //equate with yr_no from Date_Dim Set vLastYear = extract(year from sysdate)-1; //equate with yr_no from Date_Dim Set vCurrentMonth = to_char(trunc(sysdate,'mm'),'YYYYMM'); //equate with yr_mth_no from Date_Dim Set vLastMonth = to_char(add_months(trunc(sysdate,'mm'),-1),'YYYYMM'); //equate with yr_mth_no from Date_Dim Set vLast3Months = to_char(add_months(trunc(sysdate,'mm'),-3),'YYYYMM') and to_char(add_months(trunc(sysdate,'mm'),-1),'YYYYMM'); // between yr_mth_no from Date_Dim Set vLast6Months = to_char(add_months(trunc(sysdate,'mm'),-6),'YYYYMM') and to_char(add_months(trunc(sysdate,'mm'),-1),'YYYYMM'); // between yr_mth_no from Date_Dim Set vCurrentFY = (CASE WHEN EXTRACT(month from sysdate) < 7 THEN EXTRACT(year from sysdate) ELSE EXTRACT(year from sysdate)+1 END); // equate with fisc_prd_nam from Date_Dim Set vLastFY = (CASE WHEN EXTRACT(month from sysdate) < 7 THEN EXTRACT(year from sysdate)-1 ELSE EXTRACT(year from sysdate) END); // equate with fisc_prd_nam from Date_Dim
And also created an inline table with values
[Period Selection]:
Load * Inline [ Selection_Period Current Year, Last Year, Current Month, Last Month, Current Financial Year, Last Financial Year, Last 3 Months, Last 6 Months ];
Not how shall I write the set analysis to calculate the total distance travelled based on user selection from the values in inline table ?
