topic Re: YTD using set analysis or YTD function? in QlikView

YTD using set analysis or YTD function?

b_garside — Tue, 08 Oct 2013 14:45:24 GMT

I was trying to determine a common or proven YTD expression, but was not sure if it conforms to best practices.

My goal is not to do accumulation or RangeSum. I realize many do this in the script via flags, but I prefer in this case to use Set Analysis. But if flags are really the only way then I'm open to see sample code.

Is there a better/simpler way using the YTD function?

Date=is a standard date mm/dd/yyyy

Sum({$ <Date={"$(='>=' & YearStart(max({$} Date)) & '<=' & max({$} Today() ) )"}>} TotalRevenue)

Re: YTD using set analysis or YTD function?

bbi_mba_76 — Tue, 08 Oct 2013 16:07:09 GMT

Hi,

you could look at Simply create YTD, moving totals and comparisons versus Year Ago

Re: YTD using set analysis or YTD function?

Gysbert_Wassenaar — Tue, 08 Oct 2013 16:15:51 GMT

If you want to use only set analysis then consider using the Qlikview Components library to create your calendars and get a lot of very handy set modifiers for free.

If you have very large amounts of data then an AsOf table created in the script might be a better option to keep performance at an acceptable level. If you haven't found it already have a look at this document for an explanation and example qvws: Calculating rolling n-period totals, averages or other aggregations

Re: YTD using set analysis or YTD function?

jvitantonio — Tue, 08 Oct 2013 16:31:07 GMT

Hi,

I would do it like this:

sum([$<Date = {">=$(=YearStart(max(all Date))) <= $(=max(today()))"}>} TotalRevenue)

It's simpler and you avoid confusion when using to many special characters.