Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In my dashboard I have the following expression:
NUM(SUM({$<Boekjaar = {$(=MAX(Boekjaar))}, PeriodeId = {"<=$(=MAX({<Boekjaar={$(=MAX(Boekjaar))}>} PeriodeId))"}, BalansOmschrijving1 = {"Overzicht kengetallen"}, BalansOmschrijving3 = {"Tussenrekeningen voor BTW"} >} Saldo), '###.###.###.##0')
where it gives the correct value in a straight table.
I need to have the same value in my script but I receive an unknown error when executing the script:
Unknown Error
LET vBTW = NUM(NUM(SUM({$<Boekjaar = {}, PeriodeId = {"<="}, BalansOmschrijving1 = {"Overzicht kengetallen"}, BalansOmschrijving3 = {"Tussenrekeningen voor BTW"} >} Saldo), '###.###.###.##0'), '#,##0')
When I use the same expression in a straight table I get the correct value displayed:
Why doesn't work this expression in the script? The variable vBTW needs to be set to 52522 in the script.
The part of the script where the error occurred is:
LET vBTW = NUM(NUM(SUM({$<Boekjaar = {$(=MAX(Boekjaar))}, PeriodeId = {"<=$(=MAX({<Boekjaar={$(=MAX(Boekjaar))}>} PeriodeId))"}, BalansOmschrijving1 = {"Overzicht kengetallen"}, BalansOmschrijving3 = {"Tussenrekeningen voor BTW"} >} Saldo), '###.###.###.##0'), '#,##0');
You couldn't calculate in this way within the script else you need to do it within one or maybe several load-statements. Further within the script is no set analysis available and also no adhoc-variable like: $(= ...).
- Marcus
In the load script you need to use temporary table in order to store such expression value into variable.
Tmp_table: Load sum(Saldo) as Saldo Resident YourTable where PeriodeId <= $(AnotherVariable) //Return this variable with same logic and BalansOmschrijving1 = 'Overzicht kengetallen' and BalansOmschrijving3 = 'Tussenrekeningen voor BTW'; //Tmp_table includes now only 1 row, where Saldos are aggregated into one row. let vBTW = peek('Saldo', 0, 'Tmp_table');