How to make the expression by “group by” the dimension?

[#1]

Hi all,

I have a pivot table, and I want to make the expression that depends on the dimension. In my case I want sum Issue data by "group by " Material_No dimension.

But the data is depending on Production_Order dimension.

I use this code:

Sum(distinct total <MaterialNo,Status,Production_Order> Issue_Qty)

(In 1 Material has more than one production_order, in one production_Order has only one Issue_Qty value. But I want to show the sum of Issue_Qty in 1 Material.)

The code work fine when the data is different. But in Production_Order that has the same Issue_Qty values. My code is not work.

So can anyone suggest the new solution for me to make the expression by "group by" dimension?

I already attach file with this topic.

[fernandotoledo]

the problem is the "DISTINCT" inside your sum.

QlikView´s charts automatically "groups by" the given dimensions with just SUM(FIELD) and no further expressions. You only have to use <> statements when using sum(TOTAL xxx) clauses.

[#3]

Thank you for your respond,Fernando.

I already try to use

Sum(total <Production_Order> Issue_Qty)

But the result is not what I want. QlikView automatic group by follow Reject_Code dimension(In 1 Production_Order has more than 1 Reject_Code).

But I want its sum data group by Production_Order dimension.

Do you have any suggest about this problem?

[#4]

For more information.

Part No	Status	Production_Order	Rej_Code	Issue	Yield
1AH391M04432	Complete	0 1100 00001 75 0	IM05	1,152	1,128
	Complete		PL03	1,152	1,128
	Complete		PL05	1,152	1,128
	Complete		SH05	1,152	1,128
	Complete		SM15	1,152	1,128
	Complete		SM21	1,152	1,128
	Complete		SM23	1,152	1,128
	Complete	0 1100 00001 75 1	FA07	1,152	1,124
	Complete		FA12	1,152	1,124
	Complete		IM05	1,152	1,124
	Complete		IM09	1,152	1,124
	Complete		IM12	1,152	1,124
	Complete		IM14	1,152	1,124
	Complete		MTNB	1,152	1,124
	Complete		SM08	1,152	1,124
	Complete		SM15	1,152	1,124
	Complete		TC05	1,152	1,124
	Complete	0 1100 00001 75 2	FA07	1,152	1,116
	Complete		HA04	1,152	1,116
	Complete		IM05	1,152	1,116
	Complete		PL11	1,152	1,116
	Complete		SH05	1,152	1,116
	Complete		SM14	1,152	1,116
	Complete		TC05	1,152	1,116

Above table, show 1 PartNo and in 1 PartNo has more than 1 Production_Order. And in 1 Production_Order has more than one reject_code.

And in 1 production_order has only 1 Issue value. So when I want to group Issue by Production_Order. It's should show only 1 value per production_order(In my case show 1152).

And when I sum data it's should sum Issue group by Production_Order. But right now, when I sum data, the data is sum all Issue in Rej_code (Look like QlikView automatic group by Rej_code, but in my source, Issue is depend on Production_Order)

How can I make QlikView automatic group Issue by Production_order.

[#5]

Hi ,

Is this the expected output?

Part No 1 -- Productionorder(1751) -- Sum(Issueqty)=12672

Part No1 -- Production order(1752) -- Sum(Issueqty)=11520

Part No1 -- Production order(1750) -- Sum(Issueqty)=9216

If the above is correct, it can be achievd by

=sum( total<Production_Order>Issue_Qty)

Thanks,
Sam

[#6]

Hi ssamanta,

Thank you for your respond. But actually I wan the result output like this:

Part No 1 -- Productionorder(1751) -- Sum(Issueqty)=1152

Part No1 -- Production order(1752) -- Sum(Issueqty)=1152

Part No1 -- Production order(1750) -- Sum(Issueqty)=1152

And when sum data group by Production_Order dimension is 3456

From your result, the data is sum by Reject_Code. But actually Issue data is depend on Production_Order. But QlikView automatic get Issue value for Rej_code (Actually reject_code has no Issue value.)

How can I make QlikView ignore sum Issue group by Rej_code?

[llauses243]

Hi,

this is my offer, to use:

MIN(total <MaterialNo,Status,Production_Order> Issue_Qty)

also is possible MAX or AVG in replace of MIN

from Spain, good luck, Luis

[#8]

Hi llauses,

thank you for your respond.

I already try your code but have no luck. Its show just the min or max value. But not sum data for me. I try to use :

sum(MAX(total <MaterialNo,Status,Production_Order> Issue_Qty))

But its not work too. The problem is the same. QlikView still not allow data group by Production order.

[llauses243]

hI,

for sum to use

MIN

(Issue_Qty) * COUNT(DISTINCT Production_Order)

Luis

[#10]

Hi llauses,

Thank you very for you attention. That's work with my case

But I have another condition. I need to prepare code in the case of production_order has different Issue vale. If the Issue value are not the same value. So when I try to use "max, min or avg", its not work in this case.

The sum data will sum the max, min or avg value. So it's will ignore the other value.