Qlik sense variables in scripting

Question

I am trying to calculate rating thresholds based on the total number of criteria in my table. But for some reason I cannot get the value of the variables. I tried every combination of syntax going over the Qliksense manuals. Could you please help me how can obtain actual numbers in the Rating Mapping table by evaluating the expressions for each variable and while describing the Rating Mapping table?

[Data Structure]:
LOAD [Resource],
    [Criteria],
    [Sub-Criteria],
    [Weight],
    [Value],
([Weight]*[Value]) As [Score]
FROM [lib://econtent dashboard/Final weighted criteria 7-20-15.xlsm]
(ooxml, embedded labels, table is [Data Structure]);


LET vNumberofCriteria=$(=Count(distinct [Criteria]));
LET vThresholds=$(=vNumberofCriteria*100);
LET vTr1=$(#vThresholds);
LET vTr2=$(=2*$(#vTr1));
LET vTr3=$(=3*$(#vTr1));
LET vTr4=$(=4*$(#vTr1));
LET vTr5=$(=5*$(#vTr1));


[Rating Mapping]:
 Load * INLINE [
    Treshhold, Rating, Image Location
    0, NA, http://localhost:4848/content/default/notavailable.png
    $(#vTr1), Unsatisfactory,   http://localhost:4848/content/default/unsatisfactory.png
    $(#vTr2), Marginal, http://localhost:4848/content/default/marginal.png
    $(#vTr3), Good, http://localhost:4848/content/default/good.png
    $(#vTr4), Satisfactory, http://localhost:4848/content/default/satisfactory.png
    $(#vTr5), Superior, http://localhost:4848/content/default/superior.png
];

Thanks

Answer 1

You cannot Sum in a variable this way as it doesn't know which table you are looking at it.

You can create a temporary table to do the aggregation and then use peek to assign it to your variable.

[DataStructureCount]:
LOAD
Count(distinct [Criteria]) as CriteriaCount
RESIDENT [Data Structure];

Let vNumberofCriteria= peek('CriteriaCount',0,'DataStructureCount');

DROP TABLE DataStructureCount;