Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Any thoughts on this please??
Anything like this?
= SUM(IF(DateSale >= WEEKSTART(TODAY() - 7) AND DateSale <= WEEKEND(TODAY() - 7), Amount, 0))
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?
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))
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)
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
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?
Any more help on this?
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),