topic Rolling Average, Currency Conversion, Pivot Table - not working together in QlikView

Rolling Average, Currency Conversion, Pivot Table - not working together

avastani — Wed, 17 Mar 2010 03:55:11 GMT

I have a Pivot Table where the Months are across the top as a header instead of on the side as a row. I am using the RANGESUM(ABOVE(sum(Sales/IntermediateRate/TargetRate), 0, 3))

The user selects a TargetCurrency which gives them results in appropriate currency format. The problem I have is that the Rangesum to do the 3 month average sales amount is 0.00 when the Month-Year is across the top as a column header.

Is there a way to make the rolling average work when the Month-Year is a column header instead of the rows..

I would like to see,

Month-Year Jan-09 Feb-09 Mar-09 Apr-09

3mo Average(a) xxxx xxxx xxxx xxxx

monthly sale(b) xxxx xxxx xxxx xxxx

a/b xxxxx xxxxxx xxxxx xxxxx

and NOT,

MonthYear Expressions

Jan-09 3mo average xxxx

monthly sale xxxx

a/b xxxx

Feb-09 3mo average xxxx

monthly sale xxxx

a/b xxxx

any ideas are greatly appreciated. thanks much.

ps; i tried to do it in the script but wasn't able to achieve it because of the different currency rates applicable in each month for the (a) part of the expression.

Rolling Average, Currency Conversion, Pivot Table - not working together

michael_anthony — Wed, 17 Mar 2010 06:09:02 GMT

Will the "BEFORE" function work. It's equivalent to the ABOVE function, but works on columns to the left of the current column instead of rows above.

Rolling Average, Currency Conversion, Pivot Table - not working together

avastani — Wed, 17 Mar 2010 12:44:05 GMT

yes it does work, however, when someone transitions into a fast change to a line chart or straight table, i get 0.00 and nulls.

is there a way to determine the chart type in the expression?