Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
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