Help with using functions within an expression

[chris1987]

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

[sunny_talwar]

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.

[swuehl]

Try

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

Why do you add 1 to today's week?

[HirisH_V7]

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

[chris1987]

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

[swuehl]

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?