topic Re: Set Analysis and IF Statement in QlikView

Set Analysis and IF Statement

dthornburg — Fri, 16 Aug 2013 18:57:08 GMT

Could anyone help me convert this to a Set Analysis expression? This works fine until a year is selected and then it breaks. I always want to sum the invoice_line_extension for the prior year no matter what year is selected.

Works Fine - Sum (if (InYearToDate (invoice_date, Today(), -1), invoice_line_extension))

Does not work - Sum (if ({<=InYearToDate>=} (invoice_date, Today(), -1), invoice_line_extension))

Thanks.

Re: Set Analysis and IF Statement

sushil353 — Fri, 16 Aug 2013 20:14:07 GMT

hi,

First of all calculate the previous year from your date field

vLastYear=Year(Max(invoice_date))-1

also get the year in your data model say you have Year as a field..

then your expression would be

sum({<Year={''$(vLastYear)''}>} invoice_line_extension)

HTH

Sushil

Re: Set Analysis and IF Statement

CELAMBARASAN — Fri, 16 Aug 2013 21:10:44 GMT

Try with this

Sum({<invoice_date={">=$(=YearStart(Today()))<=$(=Date(Today() -1))"}>} invoice_line_extension)

Re: Set Analysis and IF Statement

dthornburg — Wed, 21 Aug 2013 15:12:57 GMT

Hi,

Neither one of these worked. When I try an use the first suggestion I get a null value. The 2nd gives me the total for the previous year. The If statement that works without suggestions provides last years total to date not for the entire year.

Re: Set Analysis and IF Statement

Wed, 21 Aug 2013 15:50:54 GMT

Hello,

Maybe this :

Sum({<invoice_date={">=$(=YearStart(Today(), -1))<=$(=Date(AddMonths(Today(),-12) ))"}>} invoice_line_extension)

Re: Set Analysis and IF Statement

Gysbert_Wassenaar — Wed, 21 Aug 2013 16:04:19 GMT

Try: sum({<invoice_date={"=year(invoice_date)=year(today())"}>} invoice_line_extension)