Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

chris1987
New Contributor III

Help with using functions within an expression

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

Tags (1)
5 Replies

Re: Help with using functions within an expression

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.

MVP
MVP

Re: Help with using functions within an expression

Try

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


Why do you add 1 to today's week?

hirishv7
Honored Contributor

Re: Help with using functions within an expression

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,

WeekYear.PNG

Expression:

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

HTH,
PFA,

Hirish

chris1987
New Contributor III

Re: Help with using functions within an expression

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

MVP
MVP

Re: Help with using functions within an expression

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?

Community Browser