topic Total sales quantity for last complete week in QlikView

Total sales quantity for last complete week

hopkinsc — Fri, 18 Feb 2011 17:09:35 GMT

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

hopkinsc — Mon, 21 Feb 2011 10:42:25 GMT

Any thoughts on this please??

Total sales quantity for last complete week

Mon, 21 Feb 2011 11:30:25 GMT

Anything like this?

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

Total sales quantity for last complete week

hopkinsc — Mon, 21 Feb 2011 11:37:32 GMT

Hi, thanks for your reply..

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

Mon, 21 Feb 2011 12:16:04 GMT

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

hopkinsc — Mon, 21 Feb 2011 12:58:21 GMT

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

Mon, 21 Feb 2011 13:29:11 GMT

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

hopkinsc — Mon, 21 Feb 2011 14:38:34 GMT

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

hopkinsc — Mon, 21 Feb 2011 17:51:20 GMT

Any more help on this?

AW:Re: Total sales quantity for last complete week

brenner_martina — Mon, 21 Feb 2011 18:05:08 GMT

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),