Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
paulyeo11
Valued Contributor II

How to convert Set Analysis make it able to dynamic on date ?

Hi All

Marcus have help me with the below script , which work fine for Qlik Sense because in QS it does not encourage user to select month and year.

LY YTD =

sum({$<eXP={'eXP'}, date={">=$(=num(AddYears(YearStart(Today()), -1)))<=$(=num(AddYears(Today(), -1)))"}>} Amount)

YTD =

sum({$<eXP={'eXP'}, date={">=$(=num(YearStart(Today())))<=$(=num(Today()))"}>} Amount)

Now i want to implement the similar script in Qlik View , which allow user to select month and year.

I hope some one can help me convert the above script into more dynamic script , which allow to allow user change the month and year. Meaning can select month =12 and year = 2013 for view the sales change from 2012 to 2013.

Paul

1 Solution

Accepted Solutions

Re: How to convert Set Analysis make it able to dynamic on date ?

May be this:

LY YTD =

sum({$<eXP={'eXP'}, date={">=$(=num(AddYears(YearStart(Max(date)), -1)))<=$(=num(AddYears(Max(date), -1)))"}, year =, month =>} Amount)

YTD =

sum({$<eXP={'eXP'}, date={">=$(=num(YearStart(Max(date))))<=$(=num(Max(date)))"}, year =, month =>} Amount)


Update: add year =, month = as well

3 Replies
paulyeo11
Valued Contributor II

Re: How to convert Set Analysis make it able to dynamic on date ?

Hi All

Enclosed my QV doc

Re: How to convert Set Analysis make it able to dynamic on date ?

May be this:

LY YTD =

sum({$<eXP={'eXP'}, date={">=$(=num(AddYears(YearStart(Max(date)), -1)))<=$(=num(AddYears(Max(date), -1)))"}, year =, month =>} Amount)

YTD =

sum({$<eXP={'eXP'}, date={">=$(=num(YearStart(Max(date))))<=$(=num(Max(date)))"}, year =, month =>} Amount)


Update: add year =, month = as well

Re: How to convert Set Analysis make it able to dynamic on date ?

See if the attached is what you were looking for.

Best,

Sunny

Community Browser