Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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?
You're the best! Thank you!
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.
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
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?