topic determining interval in QlikView

determining interval

Thu, 02 Feb 2012 11:20:32 GMT

hi all,

i have the following problem:

i have a number: sum(hours)

i have some categories in a separate table:

from	to	categroy
1	4	minimum
5	8	few
9	12	more
13	15	maximum

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 ...

thanks for some ideas!

determining interval

jagan — Thu, 02 Feb 2012 11:48:08 GMT

Hi,

Check the IntervalMatch() function.

Regards,

Jagan.

Re: determining interval

sbaldwin — Thu, 02 Feb 2012 11:51:03 GMT

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;

determining interval

Thu, 02 Feb 2012 12:05:25 GMT

thanks for the prompt reply.

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.

would this work ?

would you know, how ?

thanks!

determining interval

sbaldwin — Thu, 02 Feb 2012 12:11:05 GMT

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)

determining interval

swuehl — Thu, 02 Feb 2012 12:17:08 GMT

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)

determining interval

Thu, 02 Feb 2012 12:52:33 GMT

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.

Re: determining interval

swuehl — Thu, 02 Feb 2012 13:40:03 GMT

Maybe you could do it dynamically, using e.g. the above mentioned aggr() function.

Please see attached sample.

Re: determining interval

swuehl — Thu, 02 Feb 2012 14:33:39 GMT

Another possibility if sum(Hours) is a total, without a dimension, is to use set analysis:

=only({<from = {"<=$(=sum(Hours))"}, to = {">=$(=sum(Hours))"} >} categroy)