topic Re: Set Expression Question in QlikView

Set Expression Question

Thu, 22 Aug 2013 14:33:28 GMT

I have the following expression and it doesn't appear to be working can anyone help me with the correct syntax????

Sum({$<[Invoice Date] <= {$(AsofDate)}> + <{$(AsofDate) - num(Due Date) >= 1 and $(AsofDate) <= 10}>} [Balance in Home Currency])

I am basically trying to put together two if conditions in set expression

if invoice date <= AsOfDate (which is a variable in MM/DD/YYYY format)

and if the AsOfDate - Due Date >= 1 and AsOfDate - Due Date <= 10

then sum(balance in home currency)

Do I have to do any formatting for AsOfDate????

Re: Set Expression Question

Gysbert_Wassenaar — Thu, 22 Aug 2013 14:43:38 GMT

This part isn't valid: <{$(AsofDate) - num(Due Date) >= 1 and $(AsofDate) <= 10}> . A set modifier needs a field name on the left side of the comparison not an expression. If Due Date is a field you could try <[Due Date] = {'=$(AsofDate) - num([Due Date]) >= 1 and $(AsofDate) <= 10'}>. Somehow I doubt it'll do what you want. You should probably try an if statement instead.

Re: Set Expression Question

Thu, 22 Aug 2013 14:55:23 GMT

I have tried this if condition but can't get it to work right:

Sum(if([Invoice Date] <= date(AsOfDate,'MM/DD/YYYY') and (num(AsOfDate) - num([Due Date]) >= 1 and num(AsofDate) - num([Due Date]) <= 10), [Balance in Home Currency]))

I managed to get this to work but when I add the additional 'and' statement I don't get anything:

Sum(if([Invoice Date] <= date(AsOfDate, 'MM/DD/YYYY'),[Balance in Home Currency]))

Re: Set Expression Question

Brice-SACCUCCI — Thu, 22 Aug 2013 15:07:48 GMT

What about the following?

Sum(if([Invoice Date] <= date(AsOfDate,'MM/DD/YYYY')

and (num(date(AsOfDate,'MM/DD/YYYY')) - num([Due Date]) >= 1

and num(date(AsOfDate,'MM/DD/YYYY')) - num([Due Date]) <= 10), [Balance in Home Currency]))

Re: Set Expression Question

Thu, 22 Aug 2013 15:34:09 GMT

It looks like this works!!!!

Thank you so much!!!

Re: Set Expression Question

Brice-SACCUCCI — Fri, 23 Aug 2013 08:07:30 GMT

Nice! Can you mark the question as solved?

Thanks,

Brice