Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)
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.
Hi,
Did you tried as below?
sum({$<range=>}units)
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)
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))))))
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.
=aggr(nodistinct sum(units)/sum(visits),range)
boom!