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.

[#2]

On the other way,

Can I make if condition to compare the Issue value in Production_Order. If the Issue value is same so use "sum Max". But if the Issue value are different, use "sum distinct".

But this still make the new problem for me, how to make condition to compare Issue value?

Sorry for my less knowledge

[#3]

Hi T,

What you can try is adding a calculated dimension after Production Order dimension and before Rej_code dimension as (= Issue_Qty ). Dont put a sum function since it wnt work in a pivot table.

Disable Issue_qty from expression list.

Chk the output.....

Thanks,
Sam

[#4 · Accepted solution]

Hi !!!!!! T

use ..............

=

sum(aggr(Distinct Issue_Qty,Production_Order

))

output is

Production_Order	=sum(aggr(Distinct Issue_Qty,Production_Order))
	3456
110000001751	1152
110000001752	1152
110000001753	1152

this is the output tht you want ,right...

I hope this ll help you

I

[llauses243]

Hi T,

getting offer's djpdon123, is solution final ? (see attach)

Luis

[#6]

To djpdon123 and llauses

This is very great!!! Thank you very much.

The solution is work for me.