I have a combo chart, that has calculated year dimension
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;
EXIT SCRIPT;
This already has the years separated by FY and YD.
I need to apply AOT (as of today) and YTD( year to date - last date of prev month) filters on these years in the below manner
For example, if we filter the year 2023 and AOT, with the current day being September 08 th, 2024, we will have the following data:
- 2021 FY: the whole year of 2021;
- 2022 FY: the whole year of 2022;
- 2022: the data from January 1st,, 2022 until September 08th, 2022 (the day before the current day, but for the year being filtered minus 1);
- 2023: the data from January 1st, 2023 until September 08th, 2023 (the day before the current day, but for the year being filtered).
The bars/lines being affected by the filter are 2022 and 2023, because the filter Year is set to 2023. The years shown as FY are 2021 and 2022, because they are before the year being filtered.
Another example: if we filter 2024 and YTD, the current day being September 08 , 2024, we would have the following data being shown:
- 2022 FY: the whole year of 2022;
- 2023 FY: the whole year of 2023;
- 2023: the data from January 1st, 2023 until Aug 31st, 2023 (the last day of the previous month, but for the year being filtered minus one);
- 2024: the data from January 1st, 2024 until Aug 31st, 2024 (the last day of the previous month of the year being filtered);
The bars/lines being affected by the filter are 2024 and 2023, because the Year filter selected is 2024. The years being shown as FY are 2022 and 2023, because those are the years before 2024.
Currently I am using something like this : if(GetFieldSelections([Selection])='AOT',$(vCreditLimitAnalyzedFY),$(vCreditLimitanalyzedPM))
the expression for AOT workds well but for YTD it doesn't work:
the incorrect expression is
(
SUM(IF(
FLG_ULT_INST='1' AND
FLG_ATIVO_ES='1' AND
FLG_ATIVO_EN='1' AND
FLG_USADO_SCORE_ES='1' AND
FLG_USADO_SCORE_EN='1' AND
(DES_STATUS_DES_FINAL='Aprovada' or DES_STATUS_DES_FINAL='Recusada') AND
DAT_FIM_REQUISICAO_FMT <= MonthEnd(AddMonths(Today(), -1)), CUR_LIMITE_REQ))
+
SUM(
IF((LEFT(COD_PROPOSTA,2)='AM' OR LEFT(COD_PROPOSTA,2)='RM')
AND
DAT_FIM_REQUISICAO_FMT <= MonthEnd(AddMonths(Today(), -1)),CUR_LIMITE_REQ))
)
/1E6
