topic Re: It Possible to do Sum inside Count AGGR Function in QlikView

It Possible to do Sum inside Count AGGR Function

jood_ahmad — Mon, 16 May 2016 08:57:27 GMT

HI All ,

it is possible to do Sum Inside Count Aggr function

COUNT(AGGR(Sum({< ITEM_GROUP={[XXXXX]} >},QUANTITY)), %Order_Key)

what is wrong in my formula .

Message was edited by: Ahmad Kastero

Re: It Possible to do Sum inside Count AGGR Function

swuehl — Mon, 16 May 2016 08:59:17 GMT

Remove the comma before QUANTITY and move a bracket to the end:

=COUNT(AGGR(Sum({< ITEM_GROUP={[XXXXX]} >} QUANTITY), %Order_Key))

But I am not sure what you want to achieve with this expression, counting a sum?

edit:

Another question: what do you want to achieve with this set modifier:

ITEM_GROUP={[XXXXX]}

if you want to do a record based field value comparison,you are probably better off with

=COUNT(AGGR( Sum(If( ITEM_GROUP=[XXXXX], QUANTITY)), %Order_Key))

Re: It Possible to do Sum inside Count AGGR Function

Chanty4u — Mon, 16 May 2016 09:00:42 GMT

did u try outside it

AGGR(count(Sum({< ITEM_GROUP={[XXXXX]} >},QUANTITY)), %Order_Key)

Re: It Possible to do Sum inside Count AGGR Function

jood_ahmad — Mon, 16 May 2016 09:12:04 GMT

i have to build a report with this :

first of all i have an Orders and this Orders are contain Items like X,Y, Z ; i need to know how many orders that have Quantity from (X) if Sum of X are =1 and how many orders that have quantity from (X) if the Sum of X are = 2

and the quantity are inserted to my DB by number 1;

so i need first to get the sum for each item group after that count the orders that meet my condition.

i hope this will clarify and clear .

Re: It Possible to do Sum inside Count AGGR Function

Kushal_Chawda — Mon, 16 May 2016 09:23:56 GMT

Still not clear what you are trying to achieve, can you try this

=count({<%Order_Key={"=sum({<ITEM_GROUP={[XXXXX]}>}QUANTITY)>0"} >}%Order_Key)

if not help then please post some sample data with required output

Re: It Possible to do Sum inside Count AGGR Function

jood_ahmad — Mon, 16 May 2016 12:50:43 GMT

unfortunately this is not working i will upload a sample.

Re: It Possible to do Sum inside Count AGGR Function

jood_ahmad — Mon, 16 May 2016 12:51:04 GMT

unfortunately this is not working i will upload a sample.

Re: It Possible to do Sum inside Count AGGR Function

Kushal_Chawda — Mon, 16 May 2016 14:10:06 GMT

This? according to your sample

Re: It Possible to do Sum inside Count AGGR Function

jood_ahmad — Mon, 16 May 2016 14:14:59 GMT

what formula you use for this.?

Re: It Possible to do Sum inside Count AGGR Function

sunny_talwar — Mon, 16 May 2016 14:15:30 GMT

I am getting a slightly different output then urs Kush.

I got 55105, 41114 and 75684 using these expressions

1)=Sum(Aggr(If(Count({<ITEM_FAMILY_GROUP = {'Large Pizza'}>} %Order_Key) = 1, 1, 0), %Order_Key))

2)=Sum(Aggr(If(Count({<ITEM_FAMILY_GROUP = {'Large Pizza'}>} %Order_Key) = 2, 1, 0), %Order_Key))

3)=Sum(Aggr(If(Count({<ITEM_FAMILY_GROUP = {'Large Pizza'}>} %Order_Key) >= 2, 1, 0), %Order_Key))

Re: It Possible to do Sum inside Count AGGR Function

Kushal_Chawda — Mon, 16 May 2016 14:22:32 GMT

what is correct Output?

Re: It Possible to do Sum inside Count AGGR Function

Kushal_Chawda — Mon, 16 May 2016 14:29:34 GMT

This seems to be correct

1) Count({<%Order_Key={"=Count({<ITEM_FAMILY_GROUP={'Large Pizza'}>}ITEM_FAMILY_GROUP)=1"}>}DISTINCT %Order_Key)

2) Count({<%Order_Key={"=Count({<ITEM_FAMILY_GROUP={'Large Pizza'}>}ITEM_FAMILY_GROUP)=2"}>}DISTINCT %Order_Key)

3) Count({<%Order_Key={"=Count({<ITEM_FAMILY_GROUP={'Large Pizza'}>}ITEM_FAMILY_GROUP)>2"}>}DISTINCT %Order_Key)

Re: It Possible to do Sum inside Count AGGR Function

sunny_talwar — Mon, 16 May 2016 14:31:43 GMT

May be this:

1) =Sum(Aggr(If(Count({<ITEM_FAMILY_GROUP = {'Large Pizza'}>}ITEM_FAMILY_GROUP) = 1, 1, 0), ORDER_NUMBER))

27452

2) =Sum(Aggr(If(Count({<ITEM_FAMILY_GROUP = {'Large Pizza'}>}ITEM_FAMILY_GROUP) = 2, 1, 0), ORDER_NUMBER))

24302

3) =Sum(Aggr(If(Count({<ITEM_FAMILY_GROUP = {'Large Pizza'}>}ITEM_FAMILY_GROUP) >= 2, 1, 0), ORDER_NUMBER))

41626

The sum of the three buckets equals 93380 which equals this -> =Count(DISTINCT ORDER_NUMBER)

Re: It Possible to do Sum inside Count AGGR Function

sunny_talwar — Mon, 16 May 2016 14:34:47 GMT

Or this without Aggr() function

1) =Count(DISTINCT {<ORDER_NUMBER = {"=Count({<ITEM_FAMILY_GROUP = {'Large Pizza'}>}ITEM_FAMILY_GROUP) = 1"}>} ORDER_NUMBER)

2) =Count(DISTINCT {<ORDER_NUMBER = {"=Count({<ITEM_FAMILY_GROUP = {'Large Pizza'}>}ITEM_FAMILY_GROUP) = 2"}>} ORDER_NUMBER)

3) =Count(DISTINCT {<ORDER_NUMBER = {"=Count({<ITEM_FAMILY_GROUP = {'Large Pizza'}>}ITEM_FAMILY_GROUP) > 2"}>} ORDER_NUMBER)

Re: It Possible to do Sum inside Count AGGR Function

Kushal_Chawda — Mon, 16 May 2016 14:40:12 GMT

We don't really know whether he wants to count % order_key or ORDER_NUMBER

Re: It Possible to do Sum inside Count AGGR Function

sunny_talwar — Mon, 16 May 2016 14:41:01 GMT

That's true

Re: It Possible to do Sum inside Count AGGR Function

jood_ahmad — Mon, 16 May 2016 15:01:41 GMT

i am just check it on data base also what i need it is the Count of %Order_Key because this is combination key in my model so no need to number of order;

i will get back with the result.

thanks all of you buddy