topic Re: Unknown error at expression while the same expression works correctly in dashboard in QlikView

Unknown error at expression while the same expression works correctly in dashboard

qlikmark1990 — Sat, 16 Nov 2024 04:40:27 GMT

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');

Re: Unknown error at expression while the same expression works correctly in dashboard

marcus_sommer — Thu, 24 Jan 2019 12:09:14 GMT

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

Re: Unknown error at expression while the same expression works correctly in dashboard

pasi_lehtinen — Thu, 24 Jan 2019 12:48:58 GMT

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');