topic Re: Using InWeek with set analysis in QlikView

Using InWeek with set analysis

Mon, 08 Sep 2014 10:49:02 GMT

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

Re: Using InWeek with set analysis

vgutkovsky — Mon, 08 Sep 2014 17:11:45 GMT

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

Re: Using InWeek with set analysis

Tue, 09 Sep 2014 10:13:25 GMT

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

Re: Using InWeek with set analysis

vgutkovsky — Tue, 09 Sep 2014 14:46:33 GMT

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

Re: Using InWeek with set analysis

Tue, 09 Sep 2014 15:18:33 GMT

Thanks Vlad - I will use your logics 🙂