Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
enzazaden
New Contributor III

Aggr and Set analysis

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:

JuneJulyAugSeptOctNovDec
15.00015.00020.000 (+5.000)20.00020.00020.00022.000 (+2.000)

1 Solution

Accepted Solutions
Employee
Employee

Re: Aggr and Set analysis

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

9 Replies
Employee
Employee

Re: Aggr and Set analysis

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

MVP
MVP

Re: Aggr and Set analysis

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
enzazaden
New Contributor III

Re: Aggr and Set analysis

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?

enzazaden
New Contributor III

Re: Aggr and Set analysis

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!

Employee
Employee

Re: Aggr and Set analysis

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

Preparing examples for Upload - Reduction and Data Scrambling

MVP
MVP

Re: Aggr and Set analysis

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

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


or

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Re: Aggr and Set analysis

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

)

idogridish2
Contributor II

Re: Aggr and Set analysis

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.

enzazaden
New Contributor III

Re: Aggr and Set analysis

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!