topic Re: USING AGGR in chart in QlikView

USING AGGR in chart

Thu, 16 May 2013 08:52:08 GMT

I have Sales pr.customer and

Production pr.customer and manufactorer.

Im trying to use the aggr function. But when to productionmanufactors have to split Sales on customer, one of the manufactors get NULL.

In the third column i cant understand why manufactor EB dont get any Sale from customer 10.

I have sent a Qlikview document with.

In my qlikview document i want to show the Sales pr.Manufactorer based on their production pr.customer.

Hope you can help

Hermann 🙂

Re: USING AGGR in chart

sushil353 — Thu, 16 May 2013 09:20:56 GMT

Hi,

Check your data table.. you dont have Revenue for kilde: EB and CustomerNR:10

That's why it is returning null value.. other wise your expression is ok..

HTH

Sushil

Re: USING AGGR in chart

kumarnatarajan — Thu, 16 May 2013 09:45:17 GMT

Hi,

Exactly i don't know what you want.

use below mentioned syntex:

Ex: =aggr(NODISTINCT Sum(Revenue),customerNR)/aggr(NODISTINCT Sum(Count),customerNR,Kilde)

Else

explain Briefly

Re: USING AGGR in chart

Thu, 16 May 2013 10:01:58 GMT

Hi Sushil.

Thank you for youre answer.

I only have revenue for customers. I want to let the production get their share of the revenue.

I want the "kilde" share of the revenue.

Customer 10 100.000

Production on customer 10 , EB 50 and BT 50, then i want to show 50.000 on EB and 50.000 on BT.

Re: USING AGGR in chart

Thu, 16 May 2013 10:11:21 GMT

Hi Kumar

Thank you for youre response.

Customer 10 has 100.000 in revenue.

Manufactor EB produces 1.000 and BT produces 2.000 to customer 10.

I want qlikview to show for EB ((100.000/3000)*1000 =33.333 and for BT ((100.000/3000)*2000 =66.667

In the 3.table TO THE RIGHT Qlikview only shows BT and they get all revenue, EB gets NUll. FOR CUSTOMER 10.

I want to show Production share of the revenue by manufactor.

Hope it helps to understand.

Re: USING AGGR in chart

Thu, 16 May 2013 10:42:55 GMT

I have used Nodistinct. BT is correct now, but EB needs its revenue share from customer 10 and 15.

aggr(NODISTINCT Sum( Revenue),customerNR)/aggr(NODISTINCT Sum(Count),customerNR))

*Sum(Count)

Re: USING AGGR in chart

lironbaram — Thu, 16 May 2013 10:52:58 GMT

hi the problem you have is due to your data structere

the simple solution is to have to seperate tables

one for production and one for sales

have a look at the attach document

Re: USING AGGR in chart

Thu, 16 May 2013 11:16:18 GMT

Hi Liron.

Thank you for youre answer my problem is that my original document contains 2 GB of data

So splitting into to tables is a little complicated. (there are a lot more fields that use in other circumstances. )

I want a chart where the only dimension is "Kilde" and then the "kilde" share of the customers revenue based on their production to the customer.

As you can see in reply under this replay. (I only want to use dimension "kilde" in chart. )

Berlingske 300.000 (Revenue, from customer 20)

BT 66.667 (2/3 revenue from customer 10)

EB (33.333+200.000) (1/3 revenue from customer 10 + revenue from customer 15)