topic Re: Calculated Dimension based on measure in QlikView

Calculated Dimension based on measure

Thu, 23 Apr 2015 09:35:42 GMT

Hi All,

I have a table which have fields like retailer name, sales ,year and month and dates. Now the requirement is to have a dimension which categorizes retailers into five segments based on their averages sales over the selected period. The average sales can be between say 0-10k, 10k-20k,20k-30k....etc. Based on the selected month and year in filters I want to have the count of retailer , their total sales in that period and the Average sales. Average Sales is the total transacted value by the no. of times or orders in which that transaction has been achieved.

Please help.

Re: Calculated Dimension based on measure

marcus_sommer — Thu, 23 Apr 2015 10:04:55 GMT

You could use class() and aggr() to create such calculated dimension, maybe in this way:

= Class(aggr(avg(Sales), Retailer), $(Cluster), 'x', 0) // maybe cluster-size as variable $(Cluster)

and the expressions could be simply sum/avg(Sales) or count(Retailer).

- Marcus

Re: Calculated Dimension based on measure

Thu, 23 Apr 2015 10:14:40 GMT

Hi Marcus,

Thanks for the reply. Can you please explain what the significance of $(Cluster) and 'x',o in the expression.

And do I need to declare any variable.

Re: Calculated Dimension based on measure

marcus_sommer — Thu, 23 Apr 2015 10:24:54 GMT

You don't need the variable. You could just use a fix value like 10000 but I like to be more flexible by this kind of clustering and use often a variable (most controlled by a inputbox in dropdown-mode with a predefined valuelist). The 'x' is an optional setting for the displayed symbol within the cluster. You could also use a num() or replace() around the expression to adjust the results.

- Marcus

Re: Calculated Dimension based on measure

Thu, 23 Apr 2015 10:29:35 GMT

Actually my requirement is not a fix interval but its like 0-40000, 40000-60000, and like that I wrote a calculated dimension as

=If(aggr(sum(value),ret_id)>=0 and aggr(sum(value),ret_id)<=40000,'a. 0K~40K',

if(aggr(sum(value),ret_id)>40000 and aggr(sum(value),ret_id)<=60000,'b. 40K~60K',

if(aggr(sum(value),ret_id)>60000 and aggr(sum(value),ret_id)<=80000,'c. 60K~80K',

if(aggr(sum(value),ret_id)>80000 and aggr(sum(value),ret_id)<=100000,'d. 80K~100K',

if(aggr(sum(value),ret_id)>100000 and aggr(sum(value),ret_id)<=500000,'e. 100K~500K',

if(aggr(sum(value),ret_id)>500000 and aggr(sum(value),ret_id)<=1000000,'f. 500K~1000K',

'g. ~>1000K'))))))

And I also need to display these ranges in my table.

Is there any issue with this expression it is not giving the correct result.

Re: Calculated Dimension based on measure

marcus_sommer — Thu, 23 Apr 2015 10:53:54 GMT

I think the calculated dimensions failed because the if-loop isn't wrapped from the aggr. The means the check should be inside the aggr-function.

But I wouldn't use such nested if-loop with two aggr-calculations each then this will be already extremly slow by middle-sized datasets and not (really) run with large amounts of data. I prefer to build a lookup for the expression-result, see here for examples:

Re: Aggr(sum in calculated dimension

Re: Substitute to nested ifs

- Marcus

Re: Calculated Dimension based on measure

Thu, 23 Apr 2015 10:59:33 GMT

Thanks Marcus.