topic Re: Help with using functions within an expression in QlikView

Help with using functions within an expression

chris1987 — Fri, 29 Jan 2016 15:49:57 GMT

Hi,

I wondered if someone can help with the below function.

=sum({<[WeekPeriod] ={<=$(Year(today())&num(week(Today())+1,'00'))}>}[Value To Make])

I have a field called WeekPeriod which is name up of Year and Week number e.g. 2001604 is year 2016 week 04

I also have a “Value to make” column which contains values I want to sum up.

What I’m trying to do is sum up all values in “Value To Make” which are before this week (less than 201604)

I can’t seem to get the logic correct though.

Any help would be appreciated

Cheers

Chris

Re: Help with using functions within an expression

sunny_talwar — Fri, 29 Jan 2016 18:23:21 GMT

Do you have date field in your database? I would suggest using Dates instead of YearWeek to do this kind of stuff as it gets complicated when its beginning of a new year and going back becomes an issue.

Re: Help with using functions within an expression

swuehl — Fri, 29 Jan 2016 18:24:27 GMT

Try

=sum({<[WeekPeriod] ={"<=$(=Year(today())&num(week(Today())+1,'00'))"}>}[Value To Make])

Why do you add 1 to today's week?

Re: Help with using functions within an expression

HirisH_V7 — Fri, 29 Jan 2016 18:37:36 GMT

Hi,

May be like this,

Data:
LOAD *,
Mid(WeekPeriod,6,6) as Week,
Mid(WeekPeriod,1,4) as year


INLINE [
    WeekPeriod, Value to make
    201601, 868
    201602, 88
    201603, 777
    201604, 75
];

By using this at front end,

Expression:

=Sum({<Year={"$(=Max(Year))"},Week={"<$(=Week(Today()))"}>}[Value to make])

HTH,
PFA,

Hirish

Re: Help with using functions within an expression

chris1987 — Mon, 01 Feb 2016 08:15:36 GMT

Thanks for the reply. I added +1 to the weeknumber as a quickfix as I'm just testing a few reports in Qlikview. On our system if you go to excel and put in =weeknum(today()) I was getting week 5, but getting Week 4 in Qlikview? I don't know if this was just our system but as the report is in development I didn't look too much into it, I was focusing on the main function

Re: Help with using functions within an expression

swuehl — Mon, 01 Feb 2016 09:36:07 GMT

Week 5 started today, Feb 1st. QV uses ISO standard week numbering, which calendar system are you using?

Maybe this blog posts are of interest:

Redefining the Week Numbers‌

Redefining the Week Start

Also note that today() with default timer mode returns the date when the document was opened:

today([timer_mode] )

Returns the current date from the system clock. The timer_mode may have the following values:

0 Date at script run
1 Date at function call
2 Date when the document was opened

Default timer_mode is 2. The timer_mode = 1 should be used with caution, since it polls the operating system every second and hence could slow down the system.

Besides this, does the changed dollar sign expansion (adding the leading equal sign) works out?