Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem with Variable

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)

OR

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!

16 Replies
Not applicable
Author

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
Author

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
Author

Hi,

Did you tried as below?

 

sum({$<range=>}units)

Not applicable
Author

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
Author

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
Author

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
Author

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

boom!