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

Help to SET Analysis - get past 4 weeks

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

1 Solution

Accepted Solutions
chematos
Specialist II
Specialist II

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

View solution in original post

10 Replies
er_mohit
Master II
Master II

Try this

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

jagan
Luminary Alumni
Luminary Alumni

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.


Not applicable
Author

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>

matthewjbryant
Creator II
Creator II

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)

MK_QSL
MVP
MVP

=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... !

Not applicable
Author

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

chematos
Specialist II
Specialist II

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

matthewjbryant
Creator II
Creator II

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.

chematos
Specialist II
Specialist II

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