topic above function and accumulated expression in QlikView

above function and accumulated expression

Thu, 14 Apr 2011 17:43:27 GMT

Hi all!
I have the following problem:
i should create a straight table with one dimension,an expression like sum(Sales),another one
like sum(Sales)/sum(total Sales) with the accumulation (obtained for instance selecting the flag
"Full Accumulation" in the Expressions tab of the straight table properties) and a last column that should be
something like if(column(2)<=0.85,'A',if(column(2)<=0.95,'B','C')) and lets' call this last expression Matrix,for future reference.Notice that column(2) refers to the expression sum(Sales)/sum(total Sales) with the full accumulation.
The problem is that if i create the straight table like that, the column Matrix read for the column(2) the value
sum(Sales)/sum(total Sales) without the accumulation,that is the accumulation is just a visual data,but not the
real data referred by the "Matrix" expression.
I've tried using the following expression "rangesum(above(sum(Sales),0,NoOfRows()))/sum(total Sales)" to get the
possibility to have the shown data equal to the real data referred by column(2). This work but the problem is that i should
order the straigth table on the base of the sum(Sales) column and even though the sort tab gives the possibility to order in that way,actually the "above" function used in the "Matrix" expression forces the sort order first on the dimension.
Has anyone of you encountered a problem like that and did you manage to solve it?
Thanks in advance!
Roberto

above function and accumulated expression

johnw — Fri, 15 Apr 2011 01:22:23 GMT

Maybe sort the table exactly like it needs to be sorted, then uncheck "Allow Interactive Sort" so that the users can't break the accumulation.

above function and accumulated expression

Fri, 15 Apr 2011 09:05:55 GMT

I've tried,but it doesn't work. I think it's because of the above function,with rank(sum(Sales)/sum(total Sales) i am able to use the value

to create the rigth flag 'A','B','C',but just because for the selection done i already know, for instance, that the rank value 20 corresponds to the 85% of the sum(Sales)/sum(total sales) expression. How can i know "a priori" the exact value given by the rank function for the (for instance) percentage 85% dinamically so that for every selection done by the user i can flag correctly 'A','B' or 'C'?

above function and accumulated expression

Fri, 15 Apr 2011 14:10:13 GMT

Hi all!
I've found the solution: it's necessary to use a calculated dimension like aggr(sum(Sales),dimension)&'_'&dimension,add an expression that will be the simple dimension itself, hide the column corrisponding to the calculated dimension and imposing a decreasing sort order for the calculated dimension on the base of the following expression left(aggr(sum(Sales),dimension)&'_'&dimension,index(aggr(sum(Sales),dimension)&'_'&dimension,'_')-1).
Anyway I would like to attach an example I hope will clarify everything,but i don't know how to do that. Can you explain me?

Thanks!
Cheers!

above function and accumulated expression

johnw — Fri, 15 Apr 2011 16:51:19 GMT


roberto.figaroli wrote:Anyway I would like to attach an example I hope will clarify everything,but i don't know how to do that. Can you explain me?

When you click on "Reply", you should see three tabs - Compose, Options and Preview. The Options tab allows you to add a file attachment. That's not available if you use "Quick Reply", though. You have to use "Reply".

above function and accumulated expression

Mon, 18 Apr 2011 08:53:37 GMT

Ok!

Here you can find the example!

Cheers!