AGGR Statement WITHOUT the dimension included...

[Dayna]

Dear all,

I'm hoping this is easier than I'm making it... What we are trying to achieve, is to sum the amount of sales but by order. Now, this dimension (order) would not be included within our chart as it's massive, but we want them to sum it by this. I was thinking of this: aggr(sum([Plan Qty]),[WO ID])

But without WO ID in the dimension.. It seems to show me the correct data when WO ID is included within the dimension, but as soon as this is taken out it all vanishes..!

Please help, as this is driving me slowly crazy...!

Many thanks for all your help and assistance.

Kind Regards,
Dayna

[danielrozental]

You don't need the aggr, just do sum([Plan Qty])

[Oleg_Troyansky]

Dayna,

what data are you trying to present in your chart? If you are not including Order as a dimension, then what good does it do to summarize Sales by Order?

If you could present a mock-up of your chart, perhaps we could find a suitable solution.

The only meaningful AGGR calculation of this kind could be if you wanted an average of order size - this way tou'd pre-aggregate Sales by Order, and then average out the results - something like this:

AVG(AGGR(sum(Sales), [Order ID]))

Any other attempt to summarize data by order byt not present the order, seems meaningless to me... Am I missing something?

[#4 · Accepted solution]

Dayna,

I empathise with your problem as I had exactly the same problem myself. I had an instance where a production plant was manufacturing against Work Orders on multiple production lines but the information was being presented in daily figures (Planned Qty for each Work Order for that day and Actual Qty produced for each Work Order for that day). Unfortunately the customer wasn't interested in overall production by day but wanted to see how often each line hit the total specified Work Order figure (regardless of which day).

Data was structured as follows:

Date	Work Order ID	Production Line	Planned Qty	Actual Qty
15/10	1001	F01	10000	10000
16/10	1001	F01	20000	10050
17/10	1001	F01	50	10000
16/10	1002	F02	5000	4000

The only way I could think of to do this was using the AGGR function because, to quote the Qlikview-deity Mr Witherspoon it creates a temporary table of records that you can manipulate.

Ultimately what the customer wanted was a series of expressions demonstrating percentages of Work Orders in various states for each line but the following example shows one of the more simple requirements to calculate the number of Work Orders (per line) where the Quantity Planned for a Word Order was exceeded by the Quantity actually produced.

SUM(if(AGGR(SUM([Plan Qty]-[Act Qty]),[WO ID])<0,1,0))

If you'd like me to go into some of the more complex requirements just give me a shout.

Robin

[Dayna]

That's just what I was after, many thanks robin007, hopefully this should resolve the issue!

[#6]

I have about the same problem, but I can not solve. In attachment I put a simple example.

Thanks