topic Re: Multiple Conditions In A Set Analysis Expression in QlikView

Multiple Conditions In A Set Analysis Expression

asmithids — Thu, 20 Mar 2014 14:39:09 GMT

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

alexandros17 — Thu, 20 Mar 2014 14:49:04 GMT

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

Gysbert_Wassenaar — Thu, 20 Mar 2014 14:50:01 GMT

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

asmithids — Thu, 20 Mar 2014 14:55:01 GMT

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

alexandros17 — Thu, 20 Mar 2014 14:59:30 GMT

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

Gysbert_Wassenaar — Thu, 20 Mar 2014 15:03:22 GMT

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

asmithids — Thu, 20 Mar 2014 15:07:31 GMT

I see your point. Thank you!

Re: Multiple Conditions In A Set Analysis Expression

asmithids — Thu, 20 Mar 2014 15:08:34 GMT

Thank you Alessandro. That corrected the problem.