Sum based on condition

rniederkirchner

Hello,

I would like to modify the Perfect Line expression on my custom space, but I have no idea how I can summarize the Net Amount LC based on the sasa Shortage:

MAX([sasaPlanned Receipt Date: Sum: sasa Net Amount LC] - [sasa Planned Receipt Date: Sum:sasa Shortage] - SAVEDEXPRESSION('sasa Late'), 0)

I also tried to create an expression from the DB where only summarizes the Net Amount if Shortage = 1, but it says that Sales Schedule Line.Shortage is not valid, but I can't find the exact column.

[sasa Planned Receipt Date: Sum: sasa Net Amount LC]
WHERE
[Sales Schedule Line.Shortage] = 1

Do you have any clue how to approach this issue?

Kind regards,
Roland

Enrico Menotti

https://community.developer.infor.com/discussion/422/sum-based-on-condition

Is this derived from LN Analytics or is that a different Analytics solution? May you please post here the expression for 'sasa Late'? Is Sales Schedule Line in the grain for sasa Net Amount LC? What is the exact message about Sales Schedule Line.Shortage not being valid?

… just a few ideas.

Enrico

rniederkirchner

This is derived from LN Analytics.

I am trying to modify the calculation from the number of lines to the sum of the net amount.

sasa Late:

[sasa PlannedReceipt Date: Sum: sasa Delivered]

WHERE

[Sales ScheduleLine.Shipment Date Variance in Hours] >NUMBER(GETVARIABLE('LN_usr_ReportSalesBucketLateHours'))

This is the error message I get:

A query or expression contains an unknown column. Please contact your administrator. Sales Schedule Line.sasa Shortage

Based on the Sales Schedule Line Details I would like to summarize Net Amount where the Perfect Line is 1.

I am searching for any solution in order to achieve this.

Diane McElhiney

Adding @Johan Slagt for his input.

Enrico Menotti

Unfortunately we do not use Sales Schedules, so I don't have any data to try with, and we are still on a very old version of LN Analytics. Yet, I did find the fields you are referring to.

There is a Saved Expression 'Sales Schedule Line Actual Perfect Line', which is marked as a Measure. I believe you cannot use it in a WHERE associated to another Measure. I mean, if it were an attribute in the model (a field in a Dimension table) you could do something like:

[Sum: Amount] WHERE [Perfect] = 1

but to my knowledge that's not going to work with a Saved Expression (in place of [Perfect]).

I believe you can define a Custom Filter (in BQL Editor in Visualizer) as:

SavedExpression('Sales Schedule Line Actual Perfect Line') = 1

and then throw in the measure which summarizes the Amount. There is a Saved Expression 'Sales Schedule Line Actual Net Amount', available in the Custom Subject Area "Sales → Measures → Sales Schedule Line Actual".

If that works, then you can throw in some attribute from the Time Dimension to analyze the perfect deliveries over time.

Enrico

rniederkirchner

Thanks Enrico.

Unfortunately this didn't solve the issue.

Also, I would like to create a KPI from this measure.

Enrico Menotti

As I have mentioned, unfortunately I have no data to play with so I cannot try to solve the problem here. Yet, please try one last thing for me: please go to Query Admin and try

SELECT TOP 100 USING OUTER JOIN SavedExpression('sasa Sales Schedule Line Actual Net Amount') 'COL0' FROM [ALL] WHERE ( (SavedExpression('sasa Sales Schedule Line Actual Perfect Line') = 1) )

In my previous reply, I had forgot 'sasa' in front of the Measures.

rniederkirchner

Hello Enrico,

Thank you.

When I tried to enter into the query I got the following error message:

A query or expression contains incorrect syntax. Please contact your administrator. Error finding the SavedExpression: sasa Sales Schedule Line Actual Net Amount

rniederkirchner

Hello, I would like to summarize again the requirement and the possible solutions:

Calculate the sum ofnet amount where the perfect line is 1.

Perfect line is anexpression.

Possible solutions:

1. Modify all the expressions (Perfect Line, Late) to calculate the net amount. Issue: Shortage is a number of 0 or 1 and it needs to be multiplied by the net amount on row level.
2. Calculate the Perfect Line expression with the Net Amount on row level.

From these, the real issue is how to multiply on row level and summarize it instead of multiply the already summarized measures.