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? 

[Or]

I think you've taken the wrong approach here in general. Typically, it is not ideal to sum the values within your script. Rather, you would load the data regularly and then sum the values within your chart using e.g.

Sum("Real / Projetado (US$)")

You could then apply set analysis to restrict the data to a specific year.

However, if you load it in script, you can't apply set analysis and you also can't reference user selections - they haven't happened yet.

[Ratier]

I did it in the script because i needed to add a row with accumulated values without changing the database. My goal is to change the month Dimension so it shows all the months and the yearly accumulated. Sinc I only have 2023 data for now, there's no problem. But when 2024 comes my graphic will sum, all Jan,Fev. data from 2023  and 2024 in the bar chart and the accumulated bar will show all data from the entire database. 

I would like to make the data restricted for one year only

[Or]

If you do this in script, it won't be able to respond to user selections. You can use a Where condition in your script to limit the data being summed, though, e.g. Where Ano2 = Year(Today()).

I don't follow your comment regarding "without changing the database". Summing things on the front end doesn't change the database at all...

Since this seems to be a follow-up to your previous question, I'd suggest you visit the link I posted in that one for a solution that doesn't require any changes to the script.

[Ratier]

When I said "without changing the database" i mean that I could do the accumulated sum as a row in the Excel Spreadsheet I use as database, but I can't do that. I think that "Where Ano2 = Year(Today())." would help. I'm trying to apply it

[Or]

You could also add Group By Ano2 with the year included in the data, so that when a year is filtered, only that year's values are summed.

But as I already suggested, a better approach here would be to use the Chart Level Scripting solution. It doesn't require any script changes and it will automatically sum the values in your chart without restricting them to a specific pre-calculated time frame. For example, if your users want to view the first six months of 2022, or the annual total but only for a subset of the customers/items/whatever other dimensions, those would not be possible with your current approach.

[Ratier]

I will study the Chart Level Scripting as you suggested starting now. Thank you very much!

Could you also instruct me on where to to add the WHERE and/or GROUP BY clause in the script?

[Or]

As part of the second Load.

Resident SOMA

Where 1=1

Group By SomeField;

[Ratier]

Thank you!

Observation: I don't know why but I don't have the Chart Level Script function in my Qlik Sense Cloud or Hub. I would be right below "Alternative measures", right?

[Or · Accepted solution]

It would. You need to enable it in the app properties first. It is disabled by default.