Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

asmithbi
Contributor II

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.

Tags (1)
1 Solution

Accepted Solutions

Re: Multiple Conditions In A Set Analysis Expression

Your second expression became:

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

7 Replies

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


talk is cheap, supply exceeds demand
asmithbi
Contributor II

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

Your second expression became:

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

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


talk is cheap, supply exceeds demand
asmithbi
Contributor II

Re: Multiple Conditions In A Set Analysis Expression

I see your point.  Thank you! 

asmithbi
Contributor II

Re: Multiple Conditions In A Set Analysis Expression

Thank you Alessandro.  That corrected the problem. 

Community Browser