topic Re: Average in QlikView in QlikView

Average in QlikView

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Average in QlikView

swuehl — Thu, 30 Jun 2016 17:49:43 GMT

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.

Re: Average in QlikView

Thu, 30 Jun 2016 17:52:06 GMT

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

Re: Average in QlikView

Thu, 30 Jun 2016 20:17:11 GMT

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.

Re: Average in QlikView

swuehl — Thu, 30 Jun 2016 20:44:39 GMT

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

Re: Average in QlikView

Fri, 01 Jul 2016 14:11:15 GMT

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.

Re: Average in QlikView

swuehl — Fri, 01 Jul 2016 21:09:19 GMT

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))