Group by clause in qlikview chart

[#1]

hi,

I want to use a ' GROUP BY' in a qlikview chart (pivot table/line chart) without which my expression errors out, is there a way to do this?

If I use the expression with Group by clause in the script it works fine, but due to other constraints, I want to use this in chart itself. So is there a way I could set the Group by clause there?

Many Thanks!!

[anbu1984]

Result of your expressions in Pivot chart are grouped at Dimensions included in your chart.

Can you show your expression? Can you explain your requirement with sample data and expected output

[tresB]

Aggr() is the front-end counter-part of group by. Try to create a sample qvw and shre explaining the expected output.

[kushalthakral]

Hi Yukti

Aggr() function can be used in expression as group by function only be used at script level.

Regards

kushal T

[#5]

(((LEFT((Year([Month_Date]))*100 + Month([Month_Date]),4)) - LEFT(Min([Start Date]),4)) * 12) + (RIGHT((Year([Month_Date]))*100 + Month([Month_Date]),2)) - RIGHT(Min([Start Date]),2) + 1

This is the expression I am using which errors out. However, if I replace Min(Start Date) with [Min Start Date] and do the calculation for it in the script as :

load Min(Start date) AS [Min Start Date]

group by [item key], [item code]

RESIDENT [abc];

It works perfectly fine. So I am guessing the issue is with not using group by in the chart..

[anbu1984]

As Tresesco said, you can use Aggr() like below

Aggr(Min([Start Date]),Dim1,Dim2)

[#7]

you guys are right about the aggr function.. but I am not sure exactly where to place it.. Could you explain with respect to the expression I mentioned above... thanks!!!

[anbu1984 · Accepted solution]

(((LEFT((Year([Month_Date]))*100 + Month([Month_Date]),4)) - LEFT(Aggr(Min([Start Date]),[item key], [item code]),4)) * 12) + (RIGHT((Year([Month_Date]))*100 + Month([Month_Date]),2)) - RIGHT(Aggr(Min([Start Date]),[item key], [item code]),2) + 1

[SunilChauhan]

aggr function comes always with aggregate function like sum,count,only avg

for example sum(sales) and want to aggregare/group by Region then aggr(sum(Sales),Region).

even you could aggregate by more than one field /dimension

aggr(sum(Sales),Region,Country,City,ID).

and for you case use below

(((LEFT((Year([Month_Date]))*100 + Month([Month_Date]),4)) - LEFT(Aggr(Min([Start Date]), [item code]),4)) * 12) + (RIGHT((Year([Month_Date]))*100 + Month([Month_Date]),2)) - RIGHT(Aggr(Min([Start Date]), [item code]),2) + 1

hope this helps

[#10]

Thanks anbu.. I did this.. but doesn't seem to work.. thanks anyway..