topic Re: Help to SET Analysis - get past 4 weeks in QlikView

Help to SET Analysis - get past 4 weeks

Mon, 21 Jul 2014 06:17:00 GMT

I want to show the last 4 weeks sale in a pivot table and cant just figure out how to.

Of course I can show the field weeknumber and make a count sold items.

But I dont want to filter at all.

Is there a way to make a SET Analysis with something like this

COUNT( {$< weeknumber = { '<= lastweek', lastweek-1, lastweek-2, lastweek-3 } >}solditems)

I know its wrong but I hope it shows what I mean

Re: Help to SET Analysis - get past 4 weeks

er_mohit — Mon, 21 Jul 2014 06:23:26 GMT

Try this

COUNT( {$< weeknumber = { ">= $(=week(today())-4)<=$(=week(today()))" } >}solditems)

Re: Help to SET Analysis - get past 4 weeks

jagan — Mon, 21 Jul 2014 06:29:20 GMT

Hi,

If you have a datefield in your datamodel then try like this

=COUNT( {< WeekDimensionName=, MonthDimensionName=, DateDimensionName= { ">= $(=weekStart(today(),-4))<=$(=WeekEnd(today(), -1))" } >}solditems)

Regards,

jagan.

Re: Help to SET Analysis - get past 4 weeks

Mon, 21 Jul 2014 07:01:40 GMT

Will this not just give me solditems 4 weeks ago – I need 1 week ago, 2weeks ago, 3 weeks ago and 4 weeks ago

Fra: Mohit Sahrma

Sendt: 21. juli 2014 08:24

Til: Schou, Hans Henrik Søren (SSC009)

Emne: Re: - Help to SET Analysis - get past 4 weeks

Qlik Community<http://community.qlik.com/>

Help to SET Analysis - get past 4 weeks

reply from Mohit Sahrma<http://community.qlik.com/people/ER.Mohit?et=watches.email.thread> in App Development - View the full discussion<http://community.qlik.com/message/572243?et=watches.email.thread#572243>

Re: Help to SET Analysis - get past 4 weeks

matthewjbryant — Mon, 21 Jul 2014 07:34:26 GMT

You'll need to have 4 different expression columns all with different set analysis functions to get this. I would set up variables called vCureentWeek,...,vCurrentWeek-3 with the formulae

week(now())

week(now())-1

week(now())-2

week(now())-3

Then set up your 4 expressions as follows:

(Count({$<weeknumber = {'$(vCurrentWeek)'}>} solditems)

(Count({$<weeknumber = {'$(vCurrentWeek-1)'}>} solditems)

(Count({$<weeknumber = {'$(vCurrentWeek-2)'}>} solditems)

(Count({$<weeknumber = {'$(vCurrentWeek-3)'}>} solditems)

Re: Help to SET Analysis - get past 4 weeks

MK_QSL — Mon, 21 Jul 2014 08:21:20 GMT

=SUM({<Month = , Year = , Quarter = , Date = {'>=$(=Date(WeekStart(Today())-28))<=$(=Date(WeekEnd(Today())-7))'}>}Sales)

This will give your Last 4 Weeks Sales... Not included current week... !

Re: Help to SET Analysis - get past 4 weeks

Tue, 29 Jul 2014 12:15:38 GMT

Hi Matthew

I tried your suggestion - but cant make it work.

Why cant you do something without the variables, - like this

Count({<weeknumber = {'(week(now))'}>} solditems)

Count({<weeknumber = {'(week(now)-1)'}>} solditems)

Count({<weeknumber = {'(week(now)-2)'}>} solditems)

this doesnt work, - maybe my lack of knowledge to SET Analysis

Re: Help to SET Analysis - get past 4 weeks

chematos — Tue, 29 Jul 2014 13:36:05 GMT

Hi Soren,

You must set the variables first to use them in your expressions:

In your menu, Settings-> Variable Overview

Then you add one variable for current week (you can use vCurrentWeek as variable name, and see the expression to get the value:

=week(today())

After that, you can use the variable in your expressions as Matthew suggested:

Count({<weeknumber = {$(vCurrentWeek)}>} solditems)

Count({<weeknumber = {$(vCurrentWeek)-1}>} solditems)

Count({<weeknumber = {$(vCurrentWeek)-2}>} solditems)

Count({<weeknumber = {$(vCurrentWeek)-3}>} solditems)

I think there is no need to use quotes but you can try it anyway.

Hope this helps, regards

Jose

Re: Help to SET Analysis - get past 4 weeks

matthewjbryant — Tue, 29 Jul 2014 13:52:00 GMT

You're right - the quotes are not needed. I don't know why I got into that habit, but both work.

Did Jose's advise help you find the variable settings Søren Schou? I've never been able to do this without variables so I would always use that method.

Re: Help to SET Analysis - get past 4 weeks

chematos — Tue, 29 Jul 2014 14:22:29 GMT

Hi Matt,

I think you need quotes when you need to evaluate a string instead of a number, I am assuming that weeknumber field is in a number format.

Best regards

Re: Help to SET Analysis - get past 4 weeks

Wed, 30 Jul 2014 06:20:31 GMT

thanx both

I got it working

I didnt write the variable right, - missed the =

after that all variables worked and I´m happy

best regards