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: 
hopkinsc
Partner - Specialist III
Partner - Specialist III

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?

9 Replies
hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Any thoughts on this please??

Not applicable

Anything like this?

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

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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?





Not applicable

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





hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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)

Not applicable

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

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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?

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Any more help on this?

brenner_martina
Partner - Specialist II
Partner - Specialist II

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