Auto applying filters in a folder is possible? Bar chart problems.

[Ratier]

I have a script that goes like this:

SOMA:
LOAD
      "Ano2",
      "Mês2",
      "Mês.Ext",
      "Qua.2",
      "Sem.2",
      "Real / Projetado (US$)",
      "Demurrage Orçado TOTAL (USD)",
      "Demurrage Programado TOTAL"
FROM [lib://Drive Pessoal Rafael Ratier/13 - GPCP Qlik/Base de Dados Demurrage Qlik.xlsx]
(ooxml, embedded labels, table is MENSAL);

Temp:
LOAD
      'Anual' as "Mês2",
      'Anual' as "Mês.Ext",
      'Anual' as "Qua.2",
      'Anual' as "Sem.2",
      Sum("Real / Projetado (US$)") as "Real / Projetado (US$)",
      Sum("Demurrage Orçado TOTAL (USD)") as "Demurrage Orçado TOTAL (USD)",
      Sum("Demurrage Programado TOTAL") as "Demurrage Programado TOTAL"
Resident SOMA;

Since i only have 2023 data, the  "Real / Projetado (US$)", "Demurrage Orçado TOTAL (USD)" and "Demurrage Programado TOTAL" fields are only showing the sum for the current Year. But when 2024 comes, the bar chart I made using the mentioned fields will sum 2023 and 2024 data. I want the bar chart to show only the most recent year, unless the year field is filtered.

I have two ideas on how to fix it, but I could not do it by myself. The first one is auto applying the year (Ano2) filter each time the folder is accessed, following a logic like this one:

• If(IsNull(GetFieldSelections(Ano2)),Max(Ano2),GetFieldSelections(Ano2))

The second idea is to change the Temp: table so that it is made only with most recent data. I could use Set Analysis to do something like the code below but that's not the correct syntax for the data load editor:

Temp:
LOAD
      'Anual' as "Mês2",
      'Anual' as "Mês.Ext",
      'Anual' as "Qua.2",
      'Anual' as "Sem.2",
      Sum({<Ano2={$(=If(IsNull(GetFieldSelections(Ano2)),Max(Ano2),GetFieldSelections(Ano2)))}>}[Real / Projetado (US$)]) as "Real / Projetado (US$)",
      Sum({<Ano2={$(=If(IsNull(GetFieldSelections(Ano2)),Max(Ano2),GetFieldSelections(Ano2)))}>}[Demurrage Orçado TOTAL (USD)]) as "Demurrage Orçado TOTAL (USD)",
      Sum({<Ano2={$(=If(IsNull(GetFieldSelections(Ano2)),Max(Ano2),GetFieldSelections(Ano2)))}>}[Demurrage Programado TOTAL]) as "Demurrage Programado TOTAL"
Resident SOMA;

What should I do? 

[Ratier]

You're the best! Thank you!

[Ratier]

If I could ask you for one more help. I studied Chart Level Scripting but no matter what I do I cant replicate the exemple given in the link you showed me. I keep getting something like this:

My code is like this:

//Gets the total no of rows in the current Chart or Table
Let P = 0;
//Declaring Quarterly Variable
Let vT1Total = 0;
Let vT2Total = 0;
Let vT3Total = 0;
Let vT4Total = 0;

For J = 1 to P
//getting the value of the Dimension for a row
Let vDim = HCValue(#hc1.dimension.1, J);
//Control Statement, to check and accumulate the numbers to the right variables
if Match(vDim,'Janeiro','Fevereiro','Março') then
Let vT1Total = vT1Total + HCValue(Orçado,J);
elseif Match(vDim,'Abril','Maio','Junho') then
Let vT2Total = vT2Total + HCValue(Orçado,J);
elseif Match(vDim,'Julho','Agosto','Setembro') then
Let vT3Total = vT3Total + HCValue(Orçado,J);
elseif Match(vDim,'Outubro','Novembro','Dezembro') then
Let vT4Total = vT4Total + HCValue(Orçado,J);
end if
Next

//If $(vShowHideTotal) = 1 then
//Using the combination of Prefix and Regular statements to add the additional rows to the Hypercube
Add Load 'T1' as "Mensal", Floor($(vT1Total)) as Orçado Autogenerate 1;
Add Load 'T2' as "Mensal", Floor($(vT2Total)) as Orçado Autogenerate 1;
Add Load 'T3' as "Mensal", Floor($(vT3Total)) as Orçado Autogenerate 1;
Add Load 'T4' as "Mensal", Floor($(vT4Total)) as Orçado Autogenerate 1;
//endif

First I thought the problem was that my Excel Workbook had 3 spreadsheets and the Exemple 3 excel workbook had just one sheet but, for now, I only needed to load one table from 1 spreadsheet and I didn't even used all the fields. So my data load editor is very similar to the data load editor from "Charting Scripting Example.qvf".

I also checked the syntax of everything and it seams that the problem is in the Let P = HCNoRows() or the  Let vDim = HCValue(#hc1.dimension.1, J) part (or both) because there's nothing beeing added to the initial value of zero. 

[Or]

That example creates quarterly totals. You'll need to adapt it to just create one grand total, but that shouldn't be complicated. You can also search for this - there are solutions in Community, such as https://community.qlik.com/t5/New-to-Qlik-Sense/Chart-Level-Scripting-Use-Case/td-p/1973024

[Ratier]

I really can't understand why nothing works for me. I learned a lot with your help about the sintax needed to do chart level scripting but there's something i'm missing. I'll take a step back and try to do a grand total and then proceed to more complex examples.

In the link you just gave me, they suggest the following code:

Let vTotal = 0;
Let P = HCNoRows();
For J = 1 to P
Let vTotal = vTotal + HCValue(Sales,J);
Next
Add Load 'Total' as Division, $(vTotal) as Sales Autogenerate 1;

Since my Dimension is labeled "Mensal" and my Measure is labeled "Orçado", I changed this code to:

Let vTotal = 0;
Let P = HCNoRows();
For J = 1 to P
Let vTotal = vTotal + HCValue(Orçado,J);
Next
Add Load 'Total' as "Mensal", $(vTotal) as Orçado Autogenerate 1;

The following error occur:

"Load statement could not find target column [1268] in hypercube"

If I change to "$(vTotal)" to "Floor($(vTotal))", the error disappears but the bar chart creates a empty bar.

What am I doing wrong?