topic Re: Aggr function in QlikView

Aggr function

Mon, 30 Mar 2015 10:16:56 GMT

i have the following data.

Entry No	Item No.	Sales	Cost
1	a	100	20
1	b	100	10
1	c	100	20
1	d	100	30

Since entry No. is constant, i want Total sales of 100, but the cost should be 80.

What function can i use.

I tried aggr function. but it is not working.

Re: Aggr function

sagarkharpude — Mon, 30 Mar 2015 10:23:45 GMT

Try nodistinct

Re: Aggr function

PrashantSangle — Mon, 30 Mar 2015 10:25:37 GMT

Hi,

Create Straight table

take

Dimension => Entry No.

Expression

1: Aggr(Concat([Item No.],','),[Entry No])

2: Sum(Distinct Cost)

3: sum(Cost)

Regards

Re: Aggr function

giakoum — Mon, 30 Mar 2015 10:26:28 GMT

In a chart with Entry No as dimension :

Expression for sales : min(Sales)

Expression for Cost : Sum(Cost)

But what is your requirement exactly?

Re: Aggr function

sunny_talwar — Mon, 30 Mar 2015 10:26:32 GMT

For cost you can use this may be:

=Sum(Total <[Entry No]> Cost)

Best,

Sunny

Re: Aggr function

MK_QSL — Mon, 30 Mar 2015 10:59:50 GMT

=SUM(Aggr(SUM(DISTINCT Sales),[Entry No]))

and

=SUM(Aggr(SUM(Cost),[Entry No]))

Re: Aggr function

avinashelite — Mon, 30 Mar 2015 12:32:30 GMT

Try like this:

Aggr(sum(Cost),[Entry No])

Re: Aggr function

hic — Mon, 30 Mar 2015 14:39:18 GMT

For the Cost, you should use

Sum(Cost)

But for the sales, I must ask: What is the logic?

If one Entry Number has several sales values - which should you pick? The average?

Further, should the sales number be summed for different Entry Numbers? If so, you should use

Sum(Aggr(Avg(sales), [Entry No]))

HIC