topic Re: Aggr and Set analysis in QlikView

Aggr and Set analysis

Anonymous — Mon, 06 Jun 2016 09:46:51 GMT

Will somebody help me with the following situation?

For each contract I want to have the sum of the contract value --> sum(aggr(_Amount_Fields),Field_Number)

This sum of contract value I want to present it cumulative in time (from current month till future months) with:

Sum({< Flag Rolling Months={1}>}0.0000000001)

Sum(aggr( {< Flag Rolling Months={1} >} _Amount_Fields),Field_Number) --> What is the right way?

Example values:

June: 15.000

August: 5.000

December: 2.000

The end result will be:

June	July	Aug	Sept	Oct	Nov	Dec
15.000	15.000	20.000 (+5.000)	20.000	20.000	20.000	22.000 (+2.000)

Re: Aggr and Set analysis

Clever_Anjos — Mon, 06 Jun 2016 11:34:51 GMT

You must have an agregation expression inside your AGGR() function

sum( {< Flag Rolling Months={1} >} aggr(sum( {< Flag Rolling Months={1} >} _Amount_Fields),Field_Number))

I recommed this post: Set Analysis in the Aggr function and this Pitfalls of the Aggr function

Re: Aggr and Set analysis

jonathandienst — Mon, 06 Jun 2016 11:36:05 GMT

Neither Aggr() nor set analysis are required to do this. Do you want to this in the front end or in script? In the front end, something like this expression in a table with month as the dimension:

=RangeSum(Above(Sum(_Amount_Fields), 0, Rowno()))

Re: Aggr and Set analysis

Anonymous — Mon, 06 Jun 2016 12:14:23 GMT

Thanks for your fast reply! This is the result of your solution. How can I present the 15.000 also in the coming months Dec 2017 till dec 2018?

Re: Aggr and Set analysis

Anonymous — Mon, 06 Jun 2016 12:19:00 GMT

Thanks for your fast reply. This is the result of your solution. In this example I have 15.000 in November 2017. From this period I want to present the 15.000 in the coming months Dec 2017 till dec 2018? How can I do this?

A solution in script is also welcome!

Re: Aggr and Set analysis

Clever_Anjos — Mon, 06 Jun 2016 12:42:19 GMT

It would be easier if you share a sample of your app

Preparing examples for Upload - Reduction and Data Scrambling

Re: Aggr and Set analysis

jonathandienst — Mon, 06 Jun 2016 12:51:04 GMT

Seeing that you have miltipe dimensions, you will get better results with

=RangeSum(Above(total Sum(_Amount_Fields), 0, Rowno()))

=RangeSum(Before(total Sum(_Amount_Fields), 0, Colno()))

Re: Aggr and Set analysis

rubenmarin — Mon, 06 Jun 2016 12:58:19 GMT

Hi Enza, can you try this expression?:

If(RowNo()=1,

RangeSum(Before(Sum([_Amount_Fields]), 0, ColumnNo())),

Above(Last(RangeSum(Before(Sum([_Amount_Fields]), 0, ColumnNo())))) + RangeSum(Before(Sum([_Amount_Fields]), 0, ColumnNo()))

)

Edit:

To manage more than 2 years rows:

If(RowNo()=1,

RangeSum(Before(Sum(V), 0, ColumnNo())),

RangeSum(Above(Last(RangeSum(Before(Sum(V), 0, ColumnNo()))), 1, RowNo())) + RangeSum(Before(Sum(V), 0, ColumnNo()))

)

Re: Aggr and Set analysis

idogridish2 — Mon, 06 Jun 2016 13:13:54 GMT

hi enzazaden,

i think the problem is that you are using a pivot table and switching the places of the dimensions year and month,

the problem is the rowno() function that when switching the dimension places changed her value

(if you add an expression with rowno() in it you will see that it returns unexpected values not like 1,2,3...)

i believe that the solution is to build a new table using group by and order by year and month.

may be you can provide a sample data..

Best

Ido.

Re: Aggr and Set analysis

Anonymous — Thu, 09 Jun 2016 08:44:17 GMT

This code works! Pitfalls of the Aggr function describes the combination of two dimensions.

sum( {< Flag Rolling Months={1} >} aggr(_Amount_Fields),Field_Number,MonthYear))

Everybody thanks for helping me!