Multiple Conditions In A Set Analysis Expression

[asmithids]

Hello,

I am trying to calculate the YTD sales amount net of YTD freight charges.  The first expression calculates the YTD sales amount and the second expression calculates the freight amount.  I added them together to compute the net YTD sales.  Both expressions work with a Year and Month list box (See attached screen).

The issue is that I am not getting any numbers.  I confirmed that the first expression works, but when I add on the second expression the whole thing fails.  The second expression is a copy of the first one with the additional script at the end (see underlined text below) that represents the freight amount. 

I believe the way I added the underlined text is wrong, but not sure how to correct it.

(sum({$<Year = {$(=max(Year))}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))"}>} MainAmount)) +

(sum({$<Year = {$(=max(Year))}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))"}, Amount = {"<$(=RowType{6})>} Amount))
  

Thank you in a advance for any help on this.

[alexandros17]

If Amount is a value you are trying to set a condition on a value doing

Amount = {'<$(=RowType{6}')

What is RowType ???

Do you want to verify if AmountType is Less or Equal to a code?

Let me know

[Gysbert_Wassenaar]

At the very least you need to close the quoting and the curly braces:

Amount = {"<$(=RowType{6})"}

And I have no idea if RowType{6} is anything meaningful

[asmithids]

Hi Gysbert,

RowType is a field that contains the different amount types.  The number 6 represents the freight amount as follows

RowType = 6 which equals Freight Amount.  

[alexandros17 · Accepted solution]

Your second expression became:

(sum({$<Year = {$(=max(Year))}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))"}, RowType= {6}>} Amount))

[Gysbert_Wassenaar]

Then Amount={"<$(=RowType{6})"} doesn't make sense. If you want to sum the amounts where RowType is smaller than 6 you can use RowType={<6}. If you want only RowType 6 then use RowType={6}.

(sum({$<Year = {$(=max(Year))}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))"}>} MainAmount))

+

(sum({$<Year = {$(=max(Year))}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))"},RowType={6}>} Amount))

[asmithids]

I see your point.  Thank you! 

[asmithids]

Thank you Alessandro.  That corrected the problem.