Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using InWeek with set analysis

HI All,

Is it possible please to use set analysis with the function 'inweek' ? i.e. I have this to convert ..

sum( if ( inweek (MyTxnDate, today(2), -52 and DayOfWeek=Mon, Sales))

........so the 'DayOfWeek=Mon' converts to set analysis easy enough, but when I try to convert the inweek, any syntax I use throws up an error. I am trying to convert as the calculation currently is so sloooooowwwww.

Thanks in advance

Bob

1 Solution

Accepted Solutions
vgutkovsky
Master II
Master II

Just make a start and end date variable.

vRelevantSalesStartDate

=weekstart(today(2)-52*7))

vRelevantSalesEndDate

=weekend(today(2)-52*7))

Expression

sum({<MyTxnDate={">=$(vRelevantSalesStartDate)<=$(vRelevantSalesEndDate)"}>}Sales)

I'm not sure of the exact "Monday" complexities that you need to include, but whatever you need to do can just be done in these 2 variables. That's why using variables with date set analysis is so nice--saves you a lot of work with maintenance.

Regards,

Vlad

View solution in original post

4 Replies
vgutkovsky
Master II
Master II

Looks like you're missing a ) parentheses. But I would suggest you save yourself a lot of headaches and just create a variable. Looks like you're trying to get the Monday that occurred 52 weeks ago. Something like this should work:

vRelevantSalesDate

=weekstart(today(2)-52*7))

Expression

sum({<MyTxnDate={'$(vRelevantSalesDate)'}>}Sales)

Regards,

Vlad

Not applicable
Author

HI Vlad,

Thankyou - your formulae would indeed for just the monday, but then if I need the whole week i.e.

sum( if ( inweek (MyTxnDate, today(2), -52), Sales))

.. you are not aware of a 'set analysis' logic that could work with this please ?

Thanks

Bob

vgutkovsky
Master II
Master II

Just make a start and end date variable.

vRelevantSalesStartDate

=weekstart(today(2)-52*7))

vRelevantSalesEndDate

=weekend(today(2)-52*7))

Expression

sum({<MyTxnDate={">=$(vRelevantSalesStartDate)<=$(vRelevantSalesEndDate)"}>}Sales)

I'm not sure of the exact "Monday" complexities that you need to include, but whatever you need to do can just be done in these 2 variables. That's why using variables with date set analysis is so nice--saves you a lot of work with maintenance.

Regards,

Vlad

Not applicable
Author

Thanks Vlad - I will use your logics 🙂