Set Analysis to filter the data based on value from standalone inline table

Abhi_WP — Fri, 15 Nov 2024 21:31:54 GMT

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 ?

Re: Set Analysis to filter the data based on value from standalone inline table

vinieme12 — Fri, 28 Jul 2023 03:32:25 GMT

Just use Flags !

refer this

https://community.qlik.com/t5/Member-Articles/Period-Presets-Compare-Periods-on-the-fly/ta-p/1486371

topic Set Analysis to filter the data based on value from standalone inline table in App Development

Set Analysis to filter the data based on value from standalone inline table

Re: Set Analysis to filter the data based on value from standalone inline table