I have a variable which I have declared as below.
LET vFinanceYearPeriod = peek('MinFY');
I have also the variable to create a sub table using the code below. This works and I can check that the variables populated with the required values.
Temp:
SELECT CustomerType,
GLAmountOrder,
ExRateToGBP,
GBPAmountBudget,
GBPAmountInvoice,
GBPAmountOrder,
mid(FinanceYearPeriod,1,4) as FinanceYear
resident Transactions
WHERE FinanceYearPeriod = $(vFinanceYearPeriod);
My issue is that I can not use a subset of a table in the final solution. I am instead trying to use a variable that I have declared in the "Settings" - Variables "Overview" menu to calculate a Total figure that is displayed in charts and gauges. The variable has the following calculation
sum(if(FinanceYearPeriod = $(vFinanceYearPeriod),GBPAmountInvoice))
As you can see the selection on the if statement is the same as the selection on the load statement for my temporary table but it will return a value 0. If I change the above code so that the" FinanceYearPeriod = $(vFinanceYearPeriod)" becomes "FinanceYearPeriod <> $(vFinanceYearPeriod)" it will return me the total of all values.
I would be grateful if anyone could let me know what I am doing wrong ?
Many thanks to all.
Paul
