Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Group By in Qlikview


Hi All

I have data in the following format

catalog#areaSalesQty
32B567HP$10034
32B567HQ$20012
32B567HR$25056
32B567HS$55021

If you calculate the average price for each row, it will look like this

avg price
$     
  2.94
$  
  16.67
$     
  4.46
$  
  26.19

Now the requirement is to display the Min and Max Average price for each catalog#

catalog#Total SalesTotal QtyAvg Pricemaxmin
32B567H $            1,100 123 $       8.94 $    26.19 $       2.94

In SQL terms, I have to calculate the Min and Max Average, grouped by Catalog# and Area

How do I achieve this in Qlikview expression? Please advise

24 Replies
karthigayan
Creator II
Creator II

Don't add 'area' field to the straight table.Remove that field and check the values.

if area field is removed this is what i m getting:

catalog#               Total Sales     Total Qty     Avg Price      max           min

32B567H                $1,100          123               $8.94       $26.19   $2.94
shekharnil
Contributor III
Contributor III

Paramita

In qlikview to get the Groupby facility you have to use the pivot table.

No other table will do the groupby.

Use it just like the straight table and select the always expanded option in the presentation tab.

Not applicable
Author

Karthigayan

You are right, the formula works when I take the data subset for a particular catalog# and hard code it at scripting level.

But for some reason it is giving me wrong results with my entire data set (multiple catalogs). Not sure what is the problem, still debugging.

MK_QSL
MVP
MVP

have you tried my solution?

Not applicable
Author

Manish,

I am trying to see if it can be done at expression level instead of any scripting.

Scripting will work I believe.

Not applicable
Author

GroupBy.PNG.png

Min Sales =

Aggr(min(Sales/Qty),catalog#)

Max Sales =

Aggr(max(Sales/Qty),catalog#)

Thanks and Regards

Padma


MK_QSL
MVP
MVP

Create a straight table

Dimension

catalog#

Expressions

SUM(Sales)

SUM(Qty)

SUM(Sales)/SUM(Qty)

Max(Aggr(SUM(Sales)/SUM(Qty),area))

Min(Aggr(SUM(Sales)/SUM(Qty),area))

Not applicable
Author

Thank you all for your helpful suggestions. But as I mentioned for some reason it is not working in my report (Though I checked it works with a small dataset). The 2 columns just dont populate.

Average.PNG.png

(NTA is same as Area. )

I am using -

min(aggr(Sales, [Catalog #],NTA)/aggr(qty, [Catalog #] NTA))

max(aggr(Sales, [Catalog #],NTA)/aggr(qty, [Catalog #], NTA))


Which works perfect in a hardcoded data set.

karthigayan
Creator II
Creator II

Paramita Sahu,

Do you require aggregation to be done on both Catalog# and NTA ???

Not applicable
Author

Kindly check this out