12 Replies Latest reply: Dec 12, 2014 2:37 AM by serhan celebi

# Analytic Functions

Hi All,

Could anyone tell me if we can use aggr function in QlikView scripting?

Regards,

Gokul

• ###### Re: Analytic Functions

Hi,

In script you can use Group By instead of Aggr in load script for aggregating the data and you can use all Aggregation Functions

( Sum,Count,Avg,Only,Mode,firstsortedvalue, Concat ) etc

Ex:-

round(Avg(TransAmount),0.05) as WeekArtNoAverages

from table.csv group by Week, ArtNo;

Regards

Anand

• ###### Re: Analytic Functions

Hello,

No you cannot.

You can use group by though. What's the scenario?

BR

Serhan

• ###### Re: Analytic Functions

Hi Serhan,

Thanks for the response.

Well the scenario goes like this: We have a Product Group column, Product Column and sales column.

We would like to sort this first based on sum of sales for product group and then for each product within that group. A product can appear in more than one group. And finally we want to do this in a Bar chart. So the ordering of the bars is by sales for product group and the ordering of groups within bars is by products sales.

So the idea was if we could get sum of Product group maybe we could use it to help in sorting bar graph.

 Product Group Product Sales e f 96 e f 34 e f 96 e f 34 e f 34 e c 65 e b 30 e a 20 f e 96 f e 34 f e 96 f e 34 f e 34 f a 20 f b 9

Regards,

Gokul

• ###### Re: Analytic Functions

if that's all you want to do...just load the table into qlik, create a bar chart with dimensions as product group and product and expression as sum(Sales). you can sort depending on the requirement in the Sort tab of object properties.

but if this has to be done on the script side which is always a good idea, use group by like the above posters suggested...

[Product Group],

[Product],

sum(Sales) as SalesTotal

from tablename

group by [Product], [Product Group];

• ###### Re: Analytic Functions

Sorting on the secondary dimension is some times difficult.

What we were able to achieve is the following:

1. We are able to align the bar graph by Product Group -- Done.

2. The categories within each bar are not getting sorted -- Pending.

Not the values on the axis:

Unsorted : 20 65 30 294

Thanks,

Gokul

• ###### Re: Analytic Functions

Should definitely go for sorting "by Expression" and "Aggr()" as guys already suggested.

BR

Serhan

• ###### Re: Analytic Functions

Hi,

Check this links, Aggr() works similarly to Group by.  Suppose if you want to sum up the sales of customers who has sold more that 1 million then Aggr() will help

=Sum(Aggr(If(Sum(Sales)> 1000000, Sum(Sales), 0), Customer))  --This is just a sample, this can be achieved in many other ways

Aggr() - Will loop through each Customer

If() - Checks whether Sales > 1000000 for each customer

Aggr Statement White Paper

QlikView Technical Brief - AGGR

QlikView App: Aggr()

Hope this helps you.

Regards,

Jagan.

• ###### Re: Analytic Functions

Thanks Jagan. Do you by any chance have some references on how bar graph sorting options work internally?

Thanks,

Gokul

• ###### Re: Analytic Functions

Hi,

Can you attach sample file?

Regards,

Jagan.

• ###### Re: Analytic Functions

Hi Jagan,

The following thread is the same problem we are trying to solve.

Stacked Bar Chart Sorting- 2 Dimension 1 Expression

Thanks.

• ###### Re: Analytic Functions

Hi Gokul,

Try in sort tab and sort it by expression.

hope it helps.

Regards

KC

• ###### Re: Analytic Functions

Check this attached sample.