topic WTD with set analysis in QlikView

WTD with set analysis

Fri, 29 Jun 2012 10:10:00 GMT

Hi All.

Is there someone that have any solution to calculate WTD (week to date) with a set analysis expression? My datefields format is YYYYMMDD.

Thanks!

Re: WTD with set analysis

jjfabian — Fri, 29 Jun 2012 11:52:12 GMT

Hi there,

assuming that you have a calendar with a year and a week field available in data model, the following Set Analysis should give you a week to date:

$<Year= {$(=max(Year))}, Week= {"<=$(=max({<Year={$(=max(Year))}>} Week))"}>}

If you do not have Year and Week as fields in your data model, simply add them in your script:

year(<yourdatefieldhere>) as Year

week(<yourdatefieldhere>) as Week

Regards,

Jakob

Re: WTD with set analysis

Mon, 02 Jul 2012 13:50:05 GMT

Hello,

Thanks for reply. Yes I have a calender in my data model but I don't really get it to work properly. My set analysis looks like:

sum({$<Year={"$(=max(Year))"}, Week={"$(=max({<Year={$(=max(Year))}>} Week))"}>} Sales)

Do you have any suggestions?

Re: WTD with set analysis

jjfabian — Mon, 02 Jul 2012 13:59:34 GMT

Well, set analysis an dates can give you trouble. Dates have very different formats depending on what region code you use and so on. Your data in your calendar may look entirely different.

I would suggest that you "test" your set analysis somewhere else first: Use a text object here and look for what the individual parts [=max(Year) and =max(Week)] of the set analysis give when you have a year an a week selected.

In most cases this is where you can find the error in your set analysis.

Re: WTD with set analysis

Tue, 03 Jul 2012 07:10:40 GMT

Ok, I understand. The date format I use looks like 'YYYYMMDD'. Could I use the date function in the set analysis, if so where?

It works fine when I test my individual parts max(Year) and max(Week).