7 Replies Latest reply: Mar 20, 2014 11:08 AM by Alec Smith

# Multiple Conditions In A Set Analysis Expression

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.

• ###### Re: Multiple Conditions In A Set Analysis Expression

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

• ###### Re: Multiple Conditions In A Set Analysis Expression

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

• ###### Re: Multiple Conditions In A Set Analysis Expression

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.

• ###### Re: Multiple Conditions In A Set Analysis Expression

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

• ###### Re: Multiple Conditions In A Set Analysis Expression

Thank you Alessandro.  That corrected the problem.

• ###### Re: Multiple Conditions In A Set Analysis Expression

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))

• ###### Re: Multiple Conditions In A Set Analysis Expression

I see your point.  Thank you!