topic Re: Group by clause in qlikview chart in QlikView

Group by clause in qlikview chart

Fri, 05 Dec 2014 06:19:40 GMT

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

Re: Group by clause in qlikview chart

anbu1984 — Fri, 05 Dec 2014 06:25:30 GMT

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

Re: Group by clause in qlikview chart

tresB — Fri, 05 Dec 2014 06:34:10 GMT

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

Re: Group by clause in qlikview chart

kushalthakral — Fri, 05 Dec 2014 06:38:34 GMT

Hi Yukti

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

Regards

kushal T

Re: Group by clause in qlikview chart

Fri, 05 Dec 2014 06:49:33 GMT

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

Re: Group by clause in qlikview chart

anbu1984 — Fri, 05 Dec 2014 07:13:21 GMT

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

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

Re: Group by clause in qlikview chart

Fri, 05 Dec 2014 08:41:00 GMT

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

Re: Group by clause in qlikview chart

anbu1984 — Fri, 05 Dec 2014 08:44:55 GMT

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

Re: Group by clause in qlikview chart

SunilChauhan — Fri, 05 Dec 2014 08:50:05 GMT

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

Re: Group by clause in qlikview chart

Fri, 05 Dec 2014 08:50:38 GMT

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

Re: Group by clause in qlikview chart

anbu1984 — Fri, 05 Dec 2014 08:58:24 GMT

Can you post sample app

Re: Group by clause in qlikview chart

Fri, 05 Dec 2014 09:12:17 GMT

Hi,

Your expression looks weird !

I didn't get it why you use

(LEFT((Year([Month_Date]))*100 + Month([Month_Date]),4)) instead of Year([Month_Date]) ?
Same thing for the other expressions.:

Create a calendar for your start Date for exemple and LEFT(Min([Start Date]),4) would just be Min([Start Year])

Re: Group by clause in qlikview chart

Fri, 05 Dec 2014 09:27:36 GMT

thanks anbu and chauhan ... your answers work..

however, I was trying to use the expression as a calculated dimension, that's when it errors out. in the pivot table it's fine if I define it as an expression or a dimension.. but the problem arises when I want to use this expression a dimension in a line chart.. way out??? Any suggestions??

Thanks for all the help guys....