How to change loaded data based on selected filter value

[User0]

Hi
I have a qlik application and i need to shpw data based on two filter values 1. full year data 2. end of month data.
For full year (default selection), the data should be loaded till today however for second selection i.e end of month, the data should be loaded only till previous month.

The logic for the date is present here: 

LET minYEAR = 2020;
LET maxYEAR = YEAR(Today());
TmpAnosMC:
LOAD ($(minYEAR) + RowNo()) - 1 AS #_ANO_MC
AutoGenerate (1)
WHILE $(minYEAR) + RowNo() <= $(maxYEAR);

FOR EACH A IN FieldValueList('#_ANO_MC')

MasterCalendar:
LOAD ID_PROPOSTA AS ID_PROPOSTA,
'$(A)' AS #_ANO_FIM,
'$(A)' & 'YD' AS DAT_VISUAL,
0 AS #_ORDEM_VISUAL
RESIDENT FACT_KA_PROPOSTA
WHERE DAT_FIM_REQUISICAO_FMT >= DATE(DATE#('01/01/' & ('$(A)'),'DD/MM/YYYY'),'DD/MM/YYYY')

AND DAT_FIM_REQUISICAO_FMT <= DATE(DATE#(Num(DAY(Today()),'00') & '/' & Num(Month(Today()),'00') & '/' & ('$(A)'),'DD/MM/YYYY'),'DD/MM/YYYY');


For the end of month filter: the field DAT_FIM_REQUISICAO_FMT  where condition should be changed to   DAT_FIM_REQUISICAO_FMT <= Date(MonthEnd(Today(), -1), 'DD/MM/YYYY')
 AND DAT_FIM_REQUISICAO_FMT <= Today();

if this can be achieved in qlik frontend of backend , kindly help.

[pravinboniface]

@User0 For maximum flexibility, I would not restrict anything in the script.  I would let DAT_FIM_REQUISICAO_FMT  come through without applying any filters. 

I would also pass that field through the auto calendar so that you can make use of the inMTD and inYTD values.  Then I would use a toggle switch in the front end to switch from MTD to YTD.

For the toggle switch, use something like this:

=DATE(pick(
match('$(vDateView)','MTD','YTD'),
if ([DAT_FIM_REQUISICAO_FMT.autoCalendar.InMTD]=1,DAT_FIM_REQUISICAO_FMT,NULL()),
if ([DAT_FIM_REQUISICAO_FMT.autoCalendar.InYTD]=1,DAT_FIM_REQUISICAO_FMT,NULL())
)
)

Hope this helps

// Default view of YTD
LET vDateView='YTD';

TEST:
Load DATE(DATE#(DAT_FIM_REQUISICAO_FMTI,'DD/MM/YYYY')) AS DAT_FIM_REQUISICAO_FMT inline [
DAT_FIM_REQUISICAO_FMTI
01/01/2020
01/09/2021
01/01/2022
01/01/2023
01/06/2020
01/05/2021
01/07/2022
01/05/2023
];

[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
Year(Today())-Year($1) AS [YearsAgo] ,
If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
Month(Today())-Month($1) AS [MonthRelNo] ,
If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
(WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
Week(Today())-Week($1) AS [WeekRelNo] ;

DERIVE FIELDS FROM FIELDS [DAT_FIM_REQUISICAO_FMT] USING [autoCalendar] ;

[User0]

This is the entire script for the calender, 
We need to change only the data for current year i.e for 0 AS #_ORDEM_VISUAL. 

LET minYEAR = 2020;
LET maxYEAR = YEAR(Today());

TmpAnosMC:
LOAD ($(minYEAR) + RowNo()) - 1 AS #_ANO_MC
AutoGenerate (1)
WHILE $(minYEAR) + RowNo() <= $(maxYEAR);

FOR EACH A IN FieldValueList('#_ANO_MC')

MasterCalendar:
LOAD ID_PROPOSTA AS ID_PROPOSTA,
'$(A)' AS #_ANO_FIM,
'$(A)' & 'YD' AS DAT_VISUAL,
0 AS #_ORDEM_VISUAL
RESIDENT FACT_KA_PROPOSTA
WHERE DAT_FIM_REQUISICAO_FMT >= DATE(DATE#('01/01/' & ('$(A)'),'DD/MM/YYYY'),'DD/MM/YYYY')

AND DAT_FIM_REQUISICAO_FMT <= DATE(DATE#(Num(DAY(Today()),'00') & '/' & Num(Month(Today()),'00') & '/' & ('$(A)'),'DD/MM/YYYY'),'DD/MM/YYYY');


Concatenate(MasterCalendar)
LOAD ID_PROPOSTA AS ID_PROPOSTA,
'$(A)' AS #_ANO_FIM,
'$(A)'-1 & 'YD' AS DAT_VISUAL,
1 AS #_ORDEM_VISUAL
RESIDENT FACT_KA_PROPOSTA
WHERE DAT_FIM_REQUISICAO_FMT >= DATE(DATE#('01/01/' & ('$(A)'-1),'DD/MM/YYYY'),'DD/MM/YYYY')
AND DAT_FIM_REQUISICAO_FMT <= DATE(DATE#(DAY(Today()) & '/' & Num(Month(Today()),'00') & '/' & ('$(A)'-1),'DD/MM/YYYY'),'DD/MM/YYYY');

Concatenate(MasterCalendar)
LOAD ID_PROPOSTA AS ID_PROPOSTA,
'$(A)' AS #_ANO_FIM,
'$(A)'-1 & 'FY' AS DAT_VISUAL,
2 AS #_ORDEM_VISUAL
RESIDENT FACT_KA_PROPOSTA
WHERE YEAR(DAT_FIM_REQUISICAO_FMT) = ('$(A)'-1);

Concatenate(MasterCalendar)
LOAD ID_PROPOSTA AS ID_PROPOSTA,
'$(A)' AS #_ANO_FIM,
'$(A)'-2 & 'FY' AS DAT_VISUAL,
3 AS #_ORDEM_VISUAL
RESIDENT FACT_KA_PROPOSTA
WHERE YEAR(DAT_FIM_REQUISICAO_FMT) = ('$(A)'-2);

NEXT A;

DROP TABLE TmpAnosMC;