Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Problem with calculated variables in script

I am trying to add some calculated variables to a table I am inputting from an external file based on a second table which is not linked to the first, using the FieldValue.  The secondary table has eight rows, each of which should produce new calculated fields in the primary table.  I am doing the calculation in stages to keep the code from getting unwieldy.  This means creating a new table for each stage, reading

I am having two problems:

a) the first three variables calculate ok, but the rest just produce zeroes,

b) the script goes continues to load the next two stages, but then gives a "Field not found" error in relation to one of the fields (TC1) defined at a previous stage.

The script is copied below.




    Tariffs, Monthly Subscription, Overage Charge per MB, MB included

    "A", 0.40, 0.5, 1

    "B", 0.80, 0.3, 2

    "C", 2.05, 0.3, 5

    "D", 3.40, 0.3, 10

    "E", 4.40, 0.3, 50

    "F", 5.15, 0.3, 100

    "G", 3.50, 0.3, 1024

    "TBA",9999.00, 1, 1025






    "Invoice Date",


    "Home usage (Kb)",


    "Usage charges - home",

    "Usage charges - roaming",

    "Current package cost",

     FieldValue('Monthly Subscription', 1) + Rangemax(0, [Home usage (Kb)] /1024 - FieldValue('MB included', 1)) * FieldValue('Overage Charge per MB', 1) as TC1,

     FieldValue('Monthly Subscription', 2) + Rangemax(0, [Home usage (Kb)] /1024 - FieldValue('MB included', 2)) * FieldValue('Overage Charge per MB', 2) as TC2,

     FieldValue('Monthly Subscription', 3) + Rangemax(0, [Home usage (Kb)] /1024 - FieldValue('MB included', 3)) * FieldValue('Overage Charge per MB', 3) as TC3,

     FieldValue('Monthly Subscription', 4) + Rangemax(0, [Home usage (Kb)] /1024 - FieldValue('MB included', 4)) * FieldValue('Overage Charge per MB', 4) as TC4,

     FieldValue('Monthly Subscription', 5) + Rangemax(0, [Home usage (Kb)] /1024 - FieldValue('MB included', 5)) * FieldValue('Overage Charge per MB', 5) as TC5,

     FieldValue('Monthly Subscription', 6) + Rangemax(0, [Home usage (Kb)] /1024 - FieldValue('MB included', 6)) * FieldValue('Overage Charge per MB', 6) as TC6,

     FieldValue('Monthly Subscription', 7) + Rangemax(0, [Home usage (Kb)] /1024 - FieldValue('MB included', 7)) * FieldValue('Overage Charge per MB', 7) as TC7,

     FieldValue('Monthly Subscription', 😎 + Rangemax(0, [Home usage (Kb)] /1024 - FieldValue('MB included', 8)) * FieldValue('Overage Charge per MB', 😎 as TC8

FROM [lib://Data for Checker/Usage extract.xlsx]

(ooxml, embedded labels, table is CH29_20150319_115647);



     sum(TC1) as TC1a,

     sum(TC2) as TC2a,

     sum(TC3) as TC3a,

     sum(TC4) as TC4a,

     sum(TC5) as TC5a,

     sum(TC6) as TC6a,

     sum(TC7) as TC7a,

     sum(TC8) as TC8a

     resident Users group by MSISDN;



     RangeMin(TC1a, TC2a, TC3a ) as Cheapest

     Resident Sums1;



     pick( match(Cheapest, TC1a, TC2a, TC3a) , FieldValue('Tariffs', 1), FieldValue('Tariffs', 2), FieldValue('Tariffs', 3)) as Best

     Resident Sums2;

Tags (1)
2 Replies
MVP & Luminary
MVP & Luminary

Re: Problem with calculated variables in script

the first three variables calculate ok, but the rest just produce zeroes,

Which variables are those? I don't see any variables defined in your script. Or are you talking about field names?

Can you post the .qvf document and the excel file?

talk is cheap, supply exceeds demand
Not applicable

Re: Problem with calculated variables in script

Sorry, yes, calculated fields.