topic Re: Problem with Variable in QlikView

Problem with Variable

Wed, 31 Jul 2013 11:03:53 GMT

Hi All,

New to QV & slowly getting to grips with the syntax for QV expressions and have come across a problem using variables.

I have a variable defined: v_subsect where the value is SUBSECTION_DESC (without an equals) using this in a straight table with a dollar sign $(v_subsect) produces the correct result in that for each line on the table (representing a product) the correct Sub-Section is diplayed.

I have come up against issues trying to use the variable in a set analysis within the same straight table.

for example to give the total revenue for the sub-section a product belongs to I tried:

sum({<SUBSECTION_DESC=$(v_subsect)>}revenue)

sum({<SUBSECTION_DESC={$(v_subsect)}>}revenue)

neither of which produced a value at all.

Does anybody have any ideas as to where I am going wrong??

Thanks

PS - whilst I understand sharing the QV would be helpful, unfortunately I cannot do so due to business restrictions!

Re: Problem with Variable

Wed, 31 Jul 2013 11:09:45 GMT

Hi Andrew,

Try this:

sum({<SUBSECTION_DESC={$(=v_subsect)}>}revenue)

Re: Problem with Variable

kaushiknsolanki — Wed, 31 Jul 2013 11:11:32 GMT

Hi,

Try puting = in your variable declaration.

Regards,

Kaushik Solanki

Re: Problem with Variable

Wed, 31 Jul 2013 11:19:53 GMT

Thanks Both - unfortunately I still get zero! which I know is not the correct answer (Since I can see the revenue for the product)

Re: Problem with Variable

Wed, 31 Jul 2013 11:25:08 GMT

Sum({$<SUBSECTION_DESC={$(=$var)}>}Revenue)

change $var

Re: Problem with Variable

Wed, 31 Jul 2013 11:26:51 GMT

Andrew, the variable returns a text? If so, try this:

sum({<SUBSECTION_DESC={$(=chr(39)&v_subsect&chr(39))}>}revenue)

Re: Problem with Variable

Tammy_Milsom — Wed, 31 Jul 2013 12:08:26 GMT

try:

sum( TOTAL {<SUBSECTION_DESC={$(v_subsect)}>}revenue)

Re: Problem with Variable

Wed, 31 Jul 2013 12:21:55 GMT

Hi all - i am currently working with Andrew on this problem. I've attached a QVW with some dummy data which hopefully highlights the problem. Any ideas would be useful. Think we've tried most of them so far.

Re: Problem with Variable

Wed, 31 Jul 2013 12:39:35 GMT

Hi,

Change the expression as below:

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

Re: Problem with Variable

Wed, 31 Jul 2013 12:43:48 GMT

alas no... That nets me the same result as:

sum({$<range>}units)

which is the total units for each product, rather than the total units for the range the product is in.

this also doesn't work

sum(all{$<range>}units)

as it totals all the ranges rather than all the products in the range.

Re: Problem with Variable

Wed, 31 Jul 2013 12:44:51 GMT

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)

Re: Problem with Variable

Wed, 31 Jul 2013 12:47:54 GMT

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.

Re: Problem with Variable

Wed, 31 Jul 2013 12:55:52 GMT

Hi,

Did you tried as below?

sum({$<range=>}units)

Re: Problem with Variable

Wed, 31 Jul 2013 13:03:06 GMT

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)

Re: Problem with Variable

Wed, 31 Jul 2013 13:14:34 GMT

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

Re: Problem with Variable

Wed, 31 Jul 2013 13:58:47 GMT

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.

Re: Problem with Variable

Wed, 31 Jul 2013 16:24:03 GMT

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

boom!