for each sum(hours) i want to determine the category and write it in a textbox or maybe a chart - i think i need to check in which from / to interval the sum(hours) value falls, but i am not sure how to do this ...
Hi, i think unfortunately becasue you are wanting to evaluate a sum there is no neat way of joining this table into your source data using the intervalmatch function (if i understand you right),
the best way i can think of is to put the above into an if statement, you could write something like the belowin your script to put it into a variable then evaluate the variable in your chart to save you writing it out.
Thanks
Steve
temp: LOAD * INLINE [ from, to, categroy 1, 4, minimum 5, 8, few 9, 12, more 13, 15, maximum ];
let var = Chr(39) & Chr(39) ;
for i =1 to NoOfRows('temp') let var = var & ' & if(floor(sum(hours)) >=' & peek('from',i-1,'temp') & ' AND floor(sum(hours)) <=' & peek('to',i-1,'temp') & ', ' & Chr(39) & peek('categroy',i-1,'temp') &Chr(39)& ')'; next;
i was wondering, if a jscript or vbscript would do it. i am not an expert, but from a logical point of view, i would need a function that i call:
getCategory(hours)
when i call this function i would pass the result of sum(hours) as parameter. the function then loops through the categories table and checks for each record if sum(hours is >= from and <= to and if it is, it returns the category.
if you were really desperate to do it with vbscript you could but remember you would rerun the script to re-evaluate your sum of hours every time any selection was made on your dashboard (which you can do no problem with triggers but personally i think you would be much better exploring the IF statement)
I understand that your categories are located in a data island, right?
If your category table is fixed, you could try creating a 'variable function', a dollar sign expansion with parameter, and hardcode your categories into the variable definition (look into dollar sign expansionwith paramater for this) using if() statements or maybe pick/match or something like this.
If your table is dynamic, loaded from a DB for example, and your sum(hours) is goruped by a dimension in a table (let's call it ID), you could compare your sum(hours) with your categories data island like
=aggr(if(sum(Hours)>=from and sum(Hours) <= to,categroy),ID, categroy)
the category table is not a data island. it is embedded in the datamodel. in my app there is another column in the category table (cattype) determining, which category set applies to one record. so the only thing i know when selecting an item is the category type and the underlying conventions (from - to - category).
both, the categories as well as the hours are loaded from external sources.
and to keep the categorization configurable, i dont want to do a huge if...else statement to determine the category for a sum(hours); i would rather do it dynamically - but it seems not feasible.
