topic Re: Aggr issue in QlikView

Aggr issue

Thu, 23 Jul 2015 08:43:00 GMT

I have the folowing dataset

MonthPart	Company	OrderID	Volume in KG	Volume in ltr
			15.819	31.122
1	Dummie	5113A1080555	2.090	4.111
1	Dummie	5174A1084003	305	600
1	Dummie	5175A1084027	441	868
1	Dummie	5180A1084257	305	600
1	Dummie	5187A1084647	362	712
1	Dummie	5187A1084701	1.252	2.464
1	Dummie	5187A1084715	293	577
1	Dummie	5187A1084720	610	1.200
1	Dummie	5188A1084740	483	950
1	Dummie	5189A1084780	483	950
1	Dummie	5195A1085169	525	1.033
2	Dummie	5191A1084782	2.927	5.758
2	Dummie	5197A1085316	3.304	6.500
2	Dummie	5201A1085458	2.439	4.799

Now i want a a aggr over the Orderid for the Volume in Ltr

So i can make the folowing calculation

If Volume in ltr for a order >5000 then volume /1000 *62.79 else volume /1000 *91.29

My result must be 2492 as total costs. But my aggr without the orderid dim doesn't work. Anybody an idea?

Re: Aggr issue

sgrice — Thu, 23 Jul 2015 08:58:06 GMT

I am unsure why you would need aggr here there OrderID is Lowest Level so

Sum(if([Volume in ltr]>5000,([Volume in ltr]/1000)*62.79,([Volume in ltr]/1000)*91.29))

Re: Aggr issue

alexandros17 — Thu, 23 Jul 2015 09:01:37 GMT

Yes, use a straight table, and do not use "Expression Total" but use "Sum of Rows" in the Expression Tab

Re: Aggr issue

Thu, 23 Jul 2015 09:06:31 GMT

for each orderid he has calculate if the total order is >5000. and then i know which Tarief ineed to choose.

Then i don't need the orderid in my table and can i calculate my total costs.

My result should look like

Company total litres total costs

Dummie 31.122 €2492

Re: Aggr issue

pokassov — Thu, 23 Jul 2015 09:18:09 GMT

sum(if([Volume in ltr]>=5000, [Volume in ltr]/1000*62.79, [Volume in ltr]/1000*91.29))

and I have 2492

May by your problem is ".' betweeen numbers?

Re: Aggr issue

Thu, 23 Jul 2015 09:26:38 GMT

for each orderid i have to calculate if the total is above 5000

then i need a result table with te total costs.

With a orderid in the table it works with every formula. But i only want a total result.

How is that possible?

Re: Aggr issue

sgrice — Thu, 23 Jul 2015 09:28:04 GMT

There is only one row per orderID so why do you need to aggr?

Re: Aggr issue

alexandros17 — Thu, 23 Jul 2015 09:29:40 GMT

As I told you before the problem is only due to totals because the expression, when computed on total has only 1 value (that is the total). This is the reason for which total must be computed as sum of all rows not as single expression

Let me know

Re: Aggr issue

Thu, 23 Jul 2015 09:36:39 GMT

I have 14 orders

From that orders i have to know the total costs

For each order id i need to now the total volume.

If the volume for an order is above 5000 then that volume from that order *tarief 1 else that volume from that order for 8 tarief to.

The caclulation has to be displayed als a total volume in a row without the orderids,

More simple then that i can't make my question

Re: Aggr issue

pokassov — Thu, 23 Jul 2015 09:40:48 GMT

As we can see each order has one row in your dataset.

What do you mean telling us total volume?

Do you have rows for items in the orders in you data model?

Re: Aggr issue

sgrice — Thu, 23 Jul 2015 09:41:35 GMT

So I take it the sum is by company month?

so is the sum of all orders volume for each company month?

Re: Aggr issue

perumal_41 — Thu, 23 Jul 2015 09:43:43 GMT

Hi Drik.

please try below expression

Sum(Aggr(sum(if([Volume in ltr]>=5000, [Volume in ltr]/1000*62.79, [Volume in ltr]/1000*91.29)),OrderID))

Re: Aggr issue

alexandros17 — Thu, 23 Jul 2015 09:43:47 GMT

This is the solution, let me know

Re: Aggr issue

pokassov — Thu, 23 Jul 2015 09:55:27 GMT

Why we need aggr?

Re: Aggr issue

Thu, 23 Jul 2015 10:00:54 GMT

Allmost good. But there is one row who gives the wrong calculation

Re: Aggr issue

perumal_41 — Thu, 23 Jul 2015 10:14:56 GMT

Hi Drik,

is possible give me scenarios .

Regards,

Perumal A

Re: Aggr issue

Thu, 23 Jul 2015 11:28:04 GMT

This is my formula for now

Sum(

Aggr(

SUM(

if(#Volume >5000, #Volume/1000*62.79, #Volume/1000*91.29))

,OrderID)

)

It works on one exeption.

Is het possible to sum the volume before comparing it with 5000.

there is one order loaded on two tanks with dimmension cust_tank.

so he calculates the formule wrong because he thinks its a 2000 ltr drop and a 4500 drop instead of 6500.

Thanks a lot.

Re: Aggr issue

perumal_41 — Thu, 23 Jul 2015 12:50:50 GMT

Hi Dirk,

Try below expression

Sum(

Aggr(

SUM(

if(#Volume >5000, #Volume/1000*62.79, #Volume/1000*91.29))

,OrderID,cust_tank)

)

Re: Aggr issue

alexandros17 — Thu, 23 Jul 2015 12:59:03 GMT

Sorry,

did you take a look to my document?