topic Re: Why is this so hard in QV? Expression not Set Analysis in QlikView

Why is this so hard in QV? Expression not Set Analysis

Mon, 09 Dec 2013 15:16:45 GMT

In a table (pivot or straight) - I would like to use an expression that behaves like Set Analysis except -it should not be based on the currently selection but rather the current "row's" dimensions.

For example - the table consists of :

STORE, SALES, YEAR, AVG_OTHER

StoreA, 25, 2012, 175/3

StoreB, 75, 2012, 125/3

StoreC, 50, 2012, 150/3

StoreD, 50, 2012, 150/3

StoreA, 125, 2013, 375/3

StoreB, 175, 2013, 325/3

StoreC, 150, 2013, 350/3

StoreD, 150, 2013, 350/3

I would like to add an expression that would Average all the Sales for the Year for every store but the store for the row (i.e. nothing to do with selection - therefore SetAnalysis would not work).

I would also like for this expresion to work outside the context of this table. Also it is not feasible to do this in the LOAD script since I will be adding so dynamic logic.

I have several of these formauls that I need that have nothing to do with the selection but rather the context of the dimensionality of the "row".

Thanks, Chuck

Re: Why is this so hard in QV? Expression not Set Analysis

Anonymous — Mon, 09 Dec 2013 15:34:20 GMT

with the use of 1 in your set anlaysis, you can ignore the current selections. the rest, depends on how you write the set analysis to calculate your expression. Post a sample app, of what exactly you are looking for. That way it will be easier..

sum( {1} Sales )
returns total sales within the application, disregarding the selection but not the dimension. If used in a chart with e.g. Products as dimension, each product will get a different value.

or like below.

sum( {1<Year = {2000}, Region = {US, SE, DE, UK, FR}>} Sales )

Re: Why is this so hard in QV? Expression not Set Analysis

Mon, 09 Dec 2013 16:07:53 GMT

This will not work at all. I do not have anything selected. Set Analysis does not work againt the current row of a table (like the TOTAL quailifier does)...it works againts the "selected" store - I am not using any selection.

Sum(TOTAL<STORE> SALES) ==> this will give the total sales across all STORES ...now I want to exclude the store in the that row. (not the selected store - there is no selected store)

Very frustrating that Set Analysis can be so powerful year simple contextual formula is so hard that can be done in BusinessObjects rather easiliy.

I will try to put together a demo example.

Re: Why is this so hard in QV? Expression not Set Analysis

Mon, 09 Dec 2013 16:15:00 GMT

Okay for this exmaple I can do

Sum(TOTAL<STORE> SALES)-Sum(SALES)

But I have came acroos so many other instances where I need to refer to the contextual rows dimensions (like YEAR-1 and not Max(YEAR)-1). The issue with Above and Below it that the formula will only work in table.

thanks.