topic Aggr Help??? in QlikView

Aggr Help???

amit_saini — Thu, 29 Mar 2018 04:02:16 GMT

Hi Folks,

I'm having below straight table , total here is "768"

Dim : Illustrator

Exp:

=(NetWorkDays(RangeMax(Min(TOTAL {<Illustrator>} Date), Max({<Illustrator>} StartedDate)), RangeMin(Max(TOTAL {<Illustrator>}Date), Max({<Illustrator>}EndDate)))*8)

I want to use this total '768"in input box for some further calculation ,but not getting the exact numbers by using aggr function.

I'm trying something like below , but getting O/p as 192 , it should be "768".

=sum(Aggr((NetWorkDays(RangeMax(Min(TOTAL {<Illustrator>} Date), Max({<Illustrator>} StartedDate)), RangeMin(Max(TOTAL {<Illustrator>}Date), Max({<Illustrator>}EndDate)))*8),Illustrator))

Please help!

Regards,

Re: Aggr Help???

tresB — Thu, 29 Mar 2018 04:46:37 GMT

Could you post your sample app to check?

Re: Aggr Help???

jonathandienst — Thu, 29 Mar 2018 05:00:55 GMT

Your syntax is OK. It looks like a problem with the data model with Illustrator not being correctly associated with the Date fields. Then 192 could be the correct result and 768 is just a table artifact created by summing the rows.

A sample app would certainly help.

Re: Aggr Help???

jonathandienst — Thu, 29 Mar 2018 05:02:33 GMT

Or try

Sum(Aggr(NoDistinct

(NetWorkDays(

RangeMax(Min(TOTAL {<Illustrator>} Date), Max({<Illustrator>} StartedDate)),

RangeMin(Max(TOTAL {<Illustrator>} Date), Max({<Illustrator>} EndDate))

)*8

), Illustrator))

Re: Aggr Help???

amit_saini — Thu, 29 Mar 2018 07:00:51 GMT

PFA!

Thanks,AS

Re: Aggr Help???

amit_saini — Thu, 29 Mar 2018 07:01:21 GMT

Sorry Jonathan,

Not working.

Thanks,

Re: Aggr Help???

MK_QSL — Thu, 29 Mar 2018 07:48:01 GMT

May be try this

=((NetWorkDays(

RangeMax(Min(TOTAL {<Illustrator>}Date),Max({<Illustrator>}StartedDate)),

RangeMin(Max(TOTAL {<Illustrator>}Date),Max({<Illustrator>}EndDate)))*8)

-vHolidays

)*COUNT({<Illustrator>}DISTINCT TOTAL Illustrator)

Re: Aggr Help???

amit_saini — Thu, 29 Mar 2018 08:30:25 GMT

Sorry Manish,

It is working just for 2018 , but if I select 2017 or other Years , numbers are different.

Thanks,

Re: Aggr Help???

MK_QSL — Thu, 29 Mar 2018 08:33:01 GMT

Try this

=SUM({<Illustrator = P(Illustrator)>}Aggr(((NetWorkDays(

RangeMax(Min(TOTAL {<Illustrator>}Date),Max({<Illustrator>}StartedDate)),

RangeMin(Max(TOTAL {<Illustrator>}Date),Max({<Illustrator>}EndDate)))*8)

-vHolidays

),Illustrator))

Re: Aggr Help???

amit_saini — Thu, 29 Mar 2018 08:38:31 GMT

Thanks Manish!

Regards,