Grouping Orders per week

[Former Member]

Hello all!

I am new to this but would love you HELP!

My Report is based on a Production Universe, I have several variables created and I want to display the data per week in a Cross tab. So, if I add the

Order # in the Vertical Axis I have no issues. But I don't want to show the orders numbers are generates many lines! Without the Order numbers, the variables don't work, they show Multivalue.

How can I group the Orders per week (2015.26/ 2015.27)

I count the orders per week but I still get Multivalue. Please help!

these are the variables with issue:

[amitrathi239 · Accepted solution]

Hi,

Check with =Sum(Count(variable)) if this will work.

Also check the variable data type is number or string?if data type is number then  use sum function is variable and see.

or data is coming correct in the crosstab with order number then try to Hide the order number and see.

Amit

[Former Member · Accepted solution]

Hi Amit,

Yes, hidden will work. Is there any other solution to group the orders? I did create a variable to Count the orders per week and the variable works but I still get multivalues if I remove the vertical axis with each Order line.

I am only adding to the Cross tab the Sum of variables. I wonder if the issue could be because I am asking to Sum an IF function.

Here is the IF variable called Max Delivery Qty.

=IF[Quantity Good Received]>[Production Schedule qty] then [Production Schedule] else [Quantity Goods Received]

I know the variables displaying Multivalues are related to Production Schedule.

Production Schedule variable:

=[Quantity Goods Received]/ [Yield Confirmed from Order]* [Total Order Qty] ForEach ([Order Number])

This variable is essentially converting the "Total Order Qty" to KG based on the conversion number extract from "Quantity Goods Received" (which is in KG) divided by "Yield confirmed" which is not in Kg)

Note: "Yield Confirmed from Order" is a Dimension with data type: number

[amitrathi239 · Accepted solution]

Hi,

Try with this.

Production Schedule variable:

=Sum([Quantity Goods Received]/ [Yield Confirmed from Order])* sum([Total Order Qty] )ForEach ([Order Number])

Break the variables and see where exactly you are getting error.

If above variable is fine then drag individual [Production Schedule] & [Quantity Goods Received] and all are fine or not.

Amit

[Former Member · Accepted solution]

Amit,

You helped me fix the Production Schedule variable!

Do you have any suggestion to fix the IF function?

=IF[Quantity Goods Received]>[Production Schedule] Then [Production Schedule] Else [Quantity Goods Received]

Thank you!

[amitrathi239 · Accepted solution]

Hi,

check firrst if values are coming in the Production Schedule & Quantity Goods Received variables.

if yes then try with these.

=sum(IF([Quantity Goods Received]>[Production Schedule]) Then [Production Schedule] Else [Quantity Goods Received])

or

=IF([Quantity Goods Received]>[Production Schedule]) Then sum([Production Schedule]) Else sum([Quantity Goods Received])

Amit

[Former Member · Accepted solution]

Yes, Production Schedule is now working and Quantity Goods Received also has the values coming in.

I tried both of your IF variable suggestion but I am still getting Multivalue

[amitrathi239 · Accepted solution]

Hi

check the datatype of both variables.it should be number..convert variables to measure variable if not and try.

Amit

[Former Member · Accepted solution]

Yes, one is a measure and the other one is a variables

[amitrathi239 · Accepted solution]

HI,

check the the data type of variable..edit the variable and see the data type.string or number.

Amit

[Former Member · Accepted solution]

Hi,

They are both numbers...what should I do?

[amitrathi239 · Accepted solution]

Hi

just apply the sum in the variable inside and make it measure variable.after that use in the if else condition and see..

Amit

[Former Member · Accepted solution]

I applied the sum of Quantity Goods Received and Sum of Production schedule inside of the IF variable:

=IF([Sum Quantity Goods Received] > [Sum Production Schedule]) Then ([Sum Quantity Goods Received]) Else ([Sum Production Schedule])

I finally got a value!

thank you so much