9 Replies Latest reply: Feb 21, 2011 1:05 PM by Martina Brenner

# Total sales quantity for last complete week

Hi,

I am trying to get the total sales qty for the last COMPLETE week.

The week being MONDAY to SUNDAY.

So if i were looking at the application on a FRIDAY then i will see sales quantities for the previous week (MON to SUN).

Is this easy to do? It would probably be achieved using SET ANALYSIS but i am not very good at SET ANALYSIS at the moment!

Can anyone help?

• ###### Total sales quantity for last complete week

Anything like this?

= SUM(IF(DateSale >= WEEKSTART(TODAY() - 7) AND DateSale <= WEEKEND(TODAY() - 7), Amount, 0))

• ###### Total sales quantity for last complete week

this is your example (but with my fields)

SUM

(IF(StoreDate >= WEEKSTART(TODAY() - 7) AND StoreDate <= WEEKEND(TODAY() - 7), SalesQuantity

, 0))

Unfortunately this brings back all zero's.

Am i doing something wrong?

Also, will this display the sales quantity from MONDAY to SUNDAY?

• ###### Total sales quantity for last complete week

WEEKSTART(date) returns the date for monday for the given date

WEEKEND(date) returns the date for sundayfor the given date

You need to explain further, are you using StoreDate as a Dimension?

Send an example of how it should look.

If you put this in a text obejct it should work.

= SUM(IF(StoreDate >= WEEKSTART(TODAY() - 7) AND StoreDate <= WEEKEND(TODAY() - 7), SalesQuantity, 0))

• ###### Total sales quantity for last complete week

Hi,

Attached is how i want it to look. The last column is the column i am trying to create. It should display sales for the previous week, so QLIKVIEW will need to know that the week starts on a MONDAY and end on a SUNDAY.

If i am looking at the application Tomoorow (22/02/11), the last colum should display the sales quantity for the last completed week (14/02/11-20/02/11)

Does this make sense?

The expression from your post is only displaying '0' (in a text box)

• ###### Total sales quantity for last complete week

The problem is that you use the date as a dimension. This limits you to the data shown for each row.

There are various solutions for this:

1. Create the value when loading the data (subselect sum for each combination of the diemnsions)

2. Take in the data 2 times, linking it on the diemnsions exept date

3. Something like this: http://community.qlik.com/forums/t/36446.aspx

• ###### Total sales quantity for last complete week

Hi,

I am confused on why it causes a problem with me using it as a dimension?

Can i not use set analysis to look at the sales per product by week number -1 (Which should give last week)

If yes, then can someone help me on how to write it please?

• ###### Total sales quantity for last complete week

Any more help on this?

• ###### AW:Re: Total sales quantity for last complete week

Hi,

Set analysis is not so difficult, in this example you get allways the Sum(Sales) kumulated from 1. day of year until last week (month, warter or year):

I have fields: Week, Month, NumMonth (numeric), Quarter and Year

and variables: varPreWeek "=(Only(Week)-1)", varPreQuarter "=(Only(Qarter)-1)", varPreMonth "=(Only(NumMonth)-1)" and
varPreYear "=Only(Year)-1)"

The user can select in the fields Week, Month, Quarter and Year

If(GetSelectedCount(Week)=1 And GetSelectedCount(Year)=1,
Sum({<Year={"<=\$(varPreYear)"},Week=>} Sales)+
Sum({<Week={"<=\$(vPreWeek)"}>} Sales),

If(GetSelectedCount(Month)=1 And GetSelectedCount(Year)=1,
Sum({<Year={"<=\$(varPreYear)"},Month=,Week=>} Sales)+
Sum({<Month+={"<=\$(varPreMonth)"}>} Sales),

If(GetSelectedCount(Quarter)=1 And GetSelectedCount(Year)=1,
Sum({<Year={"<=\$(varPreYear)"},Quarter=,Month=,Week=>} Sales)+
Sum({<Quarter+={"<=\$(varPreQuarter)"}>} Sales),

If(GetSelectedCount(Year)=1,
Sum({<Year={"<=\$(varPreYearSel)"}>} Sales),
Sum({<Year={"<=\$(varPreYear)"}>} Sales)))))

sum(sales) of last week only: Sum({<Week={\$(=Only(Week-1)}>} Sales),