Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Re: Problem with Variable

I opened the qvw file and notice that the graph is not vinculated and the value of the variable was "range". This value needs to match any value of the range.

If you want the total of range 1 the variable v_range must be Range 1, like the rows in the field range. The expression is: sum(total{$<range={$(=chr(39)&v_Range&chr(39))}>}units)

If you want the total of all you need to do like this:

sum(total units)

Not applicable

Re: Problem with Variable

i already have that one in there. Interstingly it works only when

1) i have selected a range and

2) i change the variable to be "=range" rather than "range"

in any case this is not the result we are after. We need the total units of the range that a product belongs to.

Not applicable

Re: Problem with Variable

Hi,

Did you tried as below?

 

sum({$<range=>}units)

Highlighted
Not applicable

Re: Problem with Variable

yes i have tried that. The following all return 0

sum(total{$<range={$(=v_Range)}>}units)

sum(total{$<range={$(v_Range)}>}units)

sum(total{$<range={$(=chr(39)&v_Range&chr(39))}>}units)

The following return the product level total and NOT the total for the range the product is part of.

sum({$<range={'$(v_Range)*'}>}units)

sum({$<range>}units)

sum({$<range=>}units)

Not applicable

Re: Problem with Variable

this works. However it's horrible and in real life this is not sustainable as we have thousands of "ranges".

if($(=v_Range)='range 1',sum(all{$<range={'range 1'}>}units),

if($(=v_Range)='range 2',sum(all{$<range={'range 2'}>}units),

if($(=v_Range)='range 3',sum(all{$<range={'range 3'}>}units),

if($(=v_Range)='range 4',sum(all{$<range={'range 4'}>}units),

if($(=v_Range)='range 5',sum(all{$<range={'range 5'}>}units),

if($(=v_Range)='range 6',sum(all{$<range={'range 6'}>}units),0))))))

Not applicable

Re: Problem with Variable

This is my current workaround....

in the load file create the aggregate i need and append the column to the original dataset..

myTable:

LOAD category,

     brand,

     range,

     product,

     visits,

     units

FROM

(ooxml, embedded labels, table is Sheet1);

left join

load range,

  sum(visits) as range_visits,

  sum(units) as range_units

Resident myTable

group by range;

this allows me to benchmark the product level conversion against it's parent range conversion.

Not applicable

Re: Problem with Variable

=aggr(nodistinct sum(units)/sum(visits),range)

boom!