topic Aggr: sort value by dimension in QlikView

Aggr: sort value by dimension

Mon, 26 Jul 2010 10:51:16 GMT

Hi ,

I hope somebody can help me.

This is the problem:

I need to define a calculated dimension like this:

Aggr(Sum(Value),Date)

The problem is that I noted that inside calculated dimension value are not sorted by date.

It seems that Value are sorted by random .

I need that value are sorted by date because I need to use above() function with incremental logic, inside "aggr group".

So, please give me some hints.

Thanks,

Sandro

Aggr: sort value by dimension

jonathandienst — Mon, 26 Jul 2010 12:10:10 GMT

Sandro

I am not sure about this, but perhaps you could use Date as the first dimension and Aggr(Sum(Value),Date) as the second one. This should work in a straigh table, but may not give you what you want in other chart types.

Jonathan

Aggr: sort value by dimension

jonathandienst — Mon, 26 Jul 2010 12:12:40 GMT

Another option is to use the Properties|Sort dialog: sort by Expression (using Date as the expression).

Jonathan

Aggr: sort value by dimension

Mon, 26 Jul 2010 12:20:32 GMT

Hi,

Which kind of chart do you want to create ?

It seems very suspect to use a calculated dimension as you did.

Aggr(Sum(Value),Date) is a continuous value, not a discrete one, as we usualy use in chart.

I suspect you want to display a chart with date as a dimension and an expression based on the date. Could you explain more ?

regards

JJJ

Aggr: sort value by dimension

Mon, 26 Jul 2010 14:27:10 GMT

Thank you very much for your help.

I try to explain better my porblem:

I'm working with a pivot table.

My first goal was calculate cumulative sum of series of continuous negative value.

In the following table you can see how I do it.

Date	=if(Sum(Value)>=0,0,If(Rowno()>1,Rangesum(Sum(Value),Above(Column(1))),Sum(Value)))	=Sum(Value)
	€ 0,00	€ 584.062,40
02/01/2006	-€ 1.661.046,10	-€ 1.661.046,10
03/01/2006	-€ 2.075.138,20	-€ 414.092,10
04/01/2006	€ 0,00	€ 1.746.768,60
05/01/2006	€ 0,00	€ 191.588,40
06/01/2006	-€ 456.416,50	-€ 456.416,50
09/01/2006	€ 0,00	€ 408.210,30
10/01/2006	-€ 191.029,70	-€ 191.029,70
11/01/2006	€ 0,00	€ 720.605,80
12/01/2006	-€ 877.034,70	-€ 877.034,70
13/01/2006	-€ 1.412.917,20	-€ 535.882,50
16/01/2006	-€ 1.863.735,50	-€ 450.818,30
17/01/2006	-€ 1.923.168,90	-€ 59.433,40
18/01/2006	-€ 3.498.460,80	-€ 1.575.291,90
19/01/2006	€ 0,00	€ 2.476.351,90
20/01/2006	-€ 48.092,60	-€ 48.092,60
23/01/2006	-€ 50.047,40	-€ 1.954,80
24/01/2006	€ 0,00	€ 192.470,90
25/01/2006	€ 0,00	€ 1.176.586,10
26/01/2006	€ 0,00	€ 856.686,10
27/01/2006	-€ 147.915,80	-€ 147.915,80
30/01/2006	-€ 199.185,70	-€ 51.269,90
31/01/2006	-€ 914.113,10	-€ 714.927,40

Problems start if I try to calculate the worst cumulative series by using Aggr function: =Min(Aggr(if(Sum(Value)>=0,0,If(Rowno()>1,Rangesum(Sum(Value),Above(Column(1))),Sum(Value))),Date)).

The correct result is -€ 3.498.460,80

Above function inside Aggr data set doesn't work like I need because Value are not sorted by date.

Thanks again

Sandro

Aggr: sort value by dimension

Wed, 28 Jul 2010 16:58:02 GMT

Hello, thanks for your help.

My problem is a little bit different; by using sort by expression I can get only a "exernal" sort of data on the pivot table. This works well.

The problems start when I try to calculate a Total by using Aggr function.

If I try to use inter-record function like ABOVE in my expression, and this expresion is embedded inside a Aggr group, I noted that:

- there is a reset of column index.

- My value inside Aggr data set are not sorted by date (for this reason any logic based on time position of data doesn't work).

I need to calculate Total, so I have to use aggr syntax.

Regards,

Sandro

Aggr: sort value by dimension

s_uhlig — Thu, 26 Aug 2010 09:18:34 GMT

Hi,

I hit the same problem under slightly differnt context and came to the point, that's currently not possible and should be on the roadmap for a further release (we should vote for an enhancement request). Even set analysis ala "aggr(sum({<x={"<$(=only(x))"}>}x),x)" or something like that fails for other reasons. But in my situation I could deal with load-order in the way, that I preload the expected values to a temporary table (couldn't do that in order by clause, because of dataset-size) like

Tmp:

load recno() as x autogenerate($(maxX));

, which was fast enough and simply did what I wanted.

May be, you can do the same under your conditions.

Regards

Sven