Using Total qualifier on a derived column in Pivot Table

[Anonymous]

Hello QlikView gurus,

I am writing to get help on an issue I am facing with derived columns in Pivot Table. By derived column I mean a column built up using expressions. Scenario, I have a pivot table with  3 dimensions: Category, SubCategory, Item.  There are 5 expression columns as below:-

1) Sum of Revenue

2) Sum of Units

3) Avg Price ( col 1 / col 2)

4) Suggested Price ( this value comes from a big expression)

5) % change (col4/ col 3 -1)

6) sum units/ sum total units

7) col 5 * col 6

The requirement is to show a column 8, that would be the SUM of column 7 by each Category.

For instance for Category 'Shoes', column 8 should give us the SUM of individual Items under Shoes.

I am trying using the below TOTAL qualifier to get that and syntax is correct as per QlikView however I get 0 returned on each row:

sum (  total <Category>  column(7))

I got this to work by breaking each expression leading up to column 7 (eg: column(7) = col5 * col6  ,  column 5= col4/col -1  ... and so on...)

Question:  Why can I not use column(7) to achieve this?  Is the TOTAL qualifier not valid on a derived column (here column 7). Tried doing it with rangesum and above function:

rangesum ( above(column(7),0,RowNo())

)

but its giving me a different count.

Any ideas?

Thanks

[Gysbert_Wassenaar]

Column(7) has no meaning outside the context of the chart and the dimension values of each row. That's why you can't use it in the sum function. You'll have to reuse the original expressions and the you'll have to use the aggr function too since you're going to have to nest aggregation functions.

[Anonymous]

Thanks for the reply Gysbert and you are right, breaking down the complex nested expression into its original expressions(on real column) gives the result (i tried it with test data).

In my real application, the challenge I am facing is around breaking down the expression 4 (above : Rounded Price) . It is a weighted average coming from a totally 4 different derived columns. Each of these 4 columns are further being derived step by step and all this results to a large nested ifs and use of aggregate functions. (easily 50 lines of code). I know its not a best practice to create charts with such heavy built up expressions inside it, but the business case is such.

[cjohnson]

Nakul,

Can you show us the 50 line code that is being used to create expression 4? Perhaps we can make suggestions to push some of the logic back to the script and/or make modifications/enhancements to the existing expression...

Thanks,

Camile

[Gysbert_Wassenaar]

What you can do is put expressions or parts of expressions in variables and then use the variables wherever you need the original expression. That way you need to maintain an expression only in one place and your chart expressions will be more compact (at least in terms of lines of code).

variables:

vUnits: sum(Units)

vRevenue: sum(Revenue)

vAvgPrice: $(vRevenue)/$(vUnits)

chart expression for column 3: $(vAvgPrice)

Of course debugging it means going up the chain of nested variables again. So you need to take more care testing the variables before putting them all together into the final chart expressions.

[Anonymous]

Right I have captured the big expressions in a variable and I am trying to make it work. At the moment it gives me a 0! I am pasting the expression here (this is just the column 4 (Suggested Price) :

( sum ( total <SubCategory> ( ( ( AGGR(SUM ( ( IF(FMOD( ( ( if(isnull(((1+ ( ( IF(VIT_FLAG = 1,

( if ( ( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) < $(vVITMaxDecr) , $(vVITMaxDecr),

if ( ( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) > $(vVITMaxIncr) , $(vVITMaxIncr), ( ($(eRawWeightedAvgPrice)) /

($(eCurrAvgPrice)) -1 ) ) ) ) , ( if ( ( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) < $(vNonVITMaxDecr) , $(vNonVITMaxDecr),

if ( ( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) > $(vNonVITMaxIncr) , $(vNonVITMaxIncr), ( ($(eRawWeightedAvgPrice)) /

($(eCurrAvgPrice)) -1 ) ) ) ) ) ) ) )* ( ($(eCurrAvgPrice)) ) )),0, ((1+ ( ( IF(VIT_FLAG = 1, ( if ( ( ($(eRawWeightedAvgPrice)) /

($(eCurrAvgPrice)) -1 ) < $(vVITMaxDecr) , $(vVITMaxDecr), if ( ( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) >

$(vVITMaxIncr) , $(vVITMaxIncr), ( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) ) ) ) , ( if ( ( ($(eRawWeightedAvgPrice)) /

($(eCurrAvgPrice)) -1 ) < $(vNonVITMaxDecr) , $(vNonVITMaxDecr), if ( ( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) >

$(vNonVITMaxIncr) , $(vNonVITMaxIncr), ( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) ) ) ) ) ) ) )* ( ($(eCurrAvgPrice)) ) )) ) ) ,1)<=0.69,

(ceil( ( ( if(isnull(((1+ ( ( IF(VIT_FLAG = 1, ( if ( ( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) <

$(vVITMaxDecr) , $(vVITMaxDecr), if ( ( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) > $(vVITMaxIncr) , $(vVITMaxIncr),

( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) ) ) ) , ( if ( ( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) <

$(vNonVITMaxDecr) , $(vNonVITMaxDecr), if ( ( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) > $(vNonVITMaxIncr) , $(vNonVITMaxIncr),

( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) ) ) ) ) ) ) )* ( ($(eCurrAvgPrice)) ) )),0, ((1+ ( ( IF(VIT_FLAG = 1,

( if ( ( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) < $(vVITMaxDecr) , $(vVITMaxDecr), if ( ( ($(eRawWeightedAvgPrice)) /

($(eCurrAvgPrice)) -1 ) > $(vVITMaxIncr) , $(vVITMaxIncr), ( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) ) ) ) ,

( if ( ( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) < $(vNonVITMaxDecr) , $(vNonVITMaxDecr), if ( ( ($(eRawWeightedAvgPrice)) /

($(eCurrAvgPrice)) -1 ) > $(vNonVITMaxIncr) , $(vNonVITMaxIncr), ( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) ) ) ) ) ) ) )*

( ($(eCurrAvgPrice)) ) )) ) ) ,0.1)-0.01), (ceil( ( ( if(isnull(((1+ ( ( IF(VIT_FLAG = 1, ( if ( ( ($(eRawWeightedAvgPrice)) /

($(eCurrAvgPrice)) -1 ) < $(vVITMaxDecr) , $(vVITMaxDecr), if ( ( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) > $(vVITMaxIncr) ,

$(vVITMaxIncr), ( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) ) ) ) , ( if ( ( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) <

$(vNonVITMaxDecr) , $(vNonVITMaxDecr), if ( ( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) > $(vNonVITMaxIncr) , $(vNonVITMaxIncr),

( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) ) ) ) ) ) ) )* ( ($(eCurrAvgPrice)) ) )),0, ((1+ ( ( IF(VIT_FLAG = 1,

( if ( ( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) < $(vVITMaxDecr) , $(vVITMaxDecr), if ( ( ($(eRawWeightedAvgPrice)) /

($(eCurrAvgPrice)) -1 ) > $(vVITMaxIncr) , $(vVITMaxIncr), ( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) ) ) ) ,

( if ( ( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) < $(vNonVITMaxDecr) , $(vNonVITMaxDecr), if ( ( ($(eRawWeightedAvgPrice)) /

($(eCurrAvgPrice)) -1 ) > $(vNonVITMaxIncr) , $(vNonVITMaxIncr), ( ($(eRawWeightedAvgPrice)) / ($(eCurrAvgPrice)) -1 ) ) ) ) ) ) ) )*

( ($(eCurrAvgPrice)) ) )) ) ) ) -0.01) ) ) ),SubCategory,Method,ITMNME,ITM_ID )) /

( AGGR(SUM(Sum((#Revenue))/Sum((#Units))),SubCategory,Method,ITMNME,ITM_ID )) -1) ) *

( ( aggr(sum( #Units),SubCategory,Method,ITMNME,ITM_ID) ) /

( aggr(sum( total <SubCategory> #Units),SubCategory,Method,ITMNME,ITM_ID) ) ) ) )

Breaking down each '$(e...)' variable here will make this already big expression gigantic and I can go that route if that results in correct calculations however, there are 2 things that are show stoppers:-

1) All these expressions inside expression will sit inside the column 7 above i.e :  "Sum ( total <Category> 'here')"     and to make it work I might have to wrap it with aggr (perhaps that may not always be the case) and together this all should make sense!

2) A few days ago I was working on a similar challenge (of using the expression instead of derived column) and QlikView did not quite like it. The chart kept loading and the entire application was unresponsive for about 10 mins. QlikView becomes unresponsive (which is understood) and thing to keep in mind is the fact table has 56 mil rows.

[Anonymous]

Hi Camile, I've added the expression in my reply to Gysbert below. Its basically made of expressions: eCurrAvgPrice and eRawWeightedAvgPrice .

Curr Avg price is just the (sum of sales / sum of units)

Raw Weighted Avg Price is made up of 4 expressions:-

ePriceFactor1, ePriceFactor2, ePriceFactor3 and ePriceFactor4.  These individual expression have similar nested IFs and aggregations.