Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Analytic Functions

Hi All,

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

Regards,

Gokul

12 Replies

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:-

Load Week, ArtNo,

round(Avg(TransAmount),0.05) as WeekArtNoAverages

from table.csv group by Week, ArtNo;

Regards

Anand

darkhorse
Valued Contributor

Re: Analytic Functions

Hello,

No you cannot.

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

BR

Serhan

Not applicable

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 GroupProductSales
ef96
ef34
ef96
ef34
ef34
ec65
eb30
ea20
fe96
fe34
fe96
fe34
fe34
fa20
fb9

Regards,

Gokul

jsaradhi
Valued Contributor

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...

load

[Product Group],

[Product],

sum(Sales) as SalesTotal

from tablename

group by [Product], [Product Group];

Not applicable

Re: Analytic Functions

Hi Saradhi,

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

MVP
MVP

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.

Not applicable

Re: Analytic Functions

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

Thanks,

Gokul

MVP
MVP

Re: Analytic Functions

Hi,

Can you attach sample file?

Regards,

Jagan.

Not applicable

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.

Community Browser