Average in QlikView

[#1]

Hi All, I am new to qlikview, I have 4 million rows of data, I need to find out Average for top 20,000 rows and average for next 20,000 rows, next 20,000 rows and so on.

Please let me know how to do this in Qliview Expression.

Thanks In Advance.

[swuehl]

Not sure how you define the top N rows. Maybe create an additional field when reloading:

LOAD

     Value,

     Floor(Recno() / 20000) +1 as TopN

     ...

Then create a chart with TopN as dimension and

=Avg(Value)

as expression.

[#3]

First Order the data based of fact value and add the flag to the table for each 20k rows.

LOAD

     Dim1,

     Dim2,

     Dim3,

     Amount,

     RowID() as RowNum,

     (RowID/20000)+1 as BatchFlag

Resident fact_table

Order by Amount DESC

[#4]

Thanks for your quick reply, first I am trying to calculate Rank in chart expression then I want to calculate Average for 20,000 rows. But I am having issue in Rank function.

AGGR(Rank(Sum(Adjusted_Variable)),[Actual_Variable]) 

As a result I am getting 1-4363207 for all rows.

4363207 is total number of rows.

Please advise me on this.

[swuehl]

Could you describe what you want to achieve with some few sample records and expected outcome?

[#6]

Hi Swuehl, Thanks a lot for helping me on this issue.I have data as below.

ID     Gross           Net           AdjustedNet

100    1,369,677      122,837     346,524

101     456,678         120,345     344,520

102     455,786         120,344      344,435

Data is sorted on AdjustedNet

like this I have 4 million rows.  I need an average for Gross,Net and AdjustedNet for every 20,000 rows.

In output I will get 200 rows.

Gross, Net and Adjustednet are expression in chart, so first I am planning to calculate Rank then average for every 20,000 rows.

[swuehl]

I assume you absolutely need a solution in the chart, so it's not possible to calculate the rank and/or the average in the script?

Try creating a calculated dimension like

=Aggr(Ceil(Rank(YourAdjustedNetExpression)/20000), ID)

and an expression

=Avg( Aggr( YourAdjustedNetExpression, ID))