topic Re: aggregation on a chart in QlikView

aggregation on a chart

andyrebs — Tue, 31 Mar 2015 13:25:01 GMT

I have a problem with the aggr funtion.

aggr(avg(DateArrive-DateOrder),SupplierName)

The problem is that the chart with dimention Article shows only one Article for each supplier.

I would like that the chart contain all the aricles of each supplier

What's wrong in the chart.

Thanks

Andrea

Re: aggregation on a chart

giakoum — Tue, 31 Mar 2015 13:26:07 GMT

not sure without a sample app, but try nodistinct in the aggr function

Re: aggregation on a chart

giakoum — Tue, 31 Mar 2015 13:26:44 GMT

Advanced Aggregation

There is a special function for advanced aggregations:

aggr ([ distinct | nodistinct ] [{set_expression}]expression {, dimension})

Returns a set of values of expression calculated over dimensions. The result can be compared to the expression column of a 'local chart', evaluated in the context where the aggr function resides. Each dimension must be a single field. It cannot be an expression (calculated dimension).

If the expression argument is preceded by the nodistinct qualifier, each combination of dimension values may generate more than one return value, depending on underlying data structure. If the expression argument is preceded by the distinct qualifier or if no qualifier is used at all, each combination of dimension values will generate only one return value.

By default, the aggregation function will aggregate over the set of possible records defined by the selection. An alternative set of records can be defined by a Set Analysis expression.

By using this function in Add calculated dimension... it is possible to achieve nested chart aggregation in multiple levels. See also Nested Aggregations and Related Issues.

When used in chart expressions it is possible to achieve Sum of Rows in Pivot Tables.

Examples:

aggr( sum(Sales), Country )

aggr( nodistinct sum(Sales), Country )

aggr( sum(Sales), Country, Region )

count( aggr( sum(Sales), Country ))

Re: aggregation on a chart

andyrebs — Tue, 31 Mar 2015 16:51:41 GMT

Thank you Ioannis

Unfortunately it does not work. Nodistinct keeps all the values the same for all products/articles of every suppliers.

Andrea

Re: aggregation on a chart

Tue, 31 Mar 2015 17:06:08 GMT

Hi, Can you provide a your App, so i can take a look.

Re: aggregation on a chart

lucasjsantos — Tue, 31 Mar 2015 17:12:32 GMT

What type of chart you are trying to do?

Re: aggregation on a chart

andyrebs — Tue, 31 Mar 2015 17:23:23 GMT

I attach the qvw

Re: aggregation on a chart

andyrebs — Tue, 31 Mar 2015 17:30:45 GMT

A straight table chart.

Re: aggregation on a chart

ramoncova06 — Tue, 31 Mar 2015 17:45:09 GMT

did you try with avg(total <Supplier> (DateArrive-DateOrder)) or

avg(total <Supplier,Article> (DateArrive-DateOrder))

Re: aggregation on a chart

Wed, 01 Apr 2015 09:46:02 GMT

Hi Andrea,

can you change your expression from aggr(avg(DateArrive-DateOrder),SupplierName)

aggr(avg(DateArrive-DateOrder),Supplier,Article).

You are missing the Article in your Dimension. Alternatively Ramon Covarrubias solutions also works.

Regs

Ram

Re: aggregation on a chart

andyrebs — Wed, 01 Apr 2015 12:24:59 GMT

Thank you Ramon