
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Combine sales history in excel by week number to current year from SQL
Hi
I have current transactional sales data from our CRM, and a years worth of weekly historical sales in excel. I have added columns into excel with the week number and season. I have joined this into my CRM transactional table. The CRM table has a sales date.
I would like to be able to select a week- eg week 5, and the table to display current sales up to the end of that week, with the relevant week from the excel file. How do I categorise my sales dates into these custom weeks.
I have tried an inline statement, or an if statement in my load clause and I cant seem to get it working - any ideas?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Jacqui,
It's a bit tricky to say exactly what you need without seeing the tables and code, however if you have data sorted by weeks in the excel and by date in the CRM, you can use the Week(Date) to get week numbers also for the sales dates.
And for accumulation you can use the Rangesum(Above(Sum(Sales),0,$(=Max(weeknumber))) which will sum all sales date up to and including the highest selected weeknumber.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
To convert the date to the week number, use the function Week([Your date]). It would be nice to see a fragment of your original data to more accurately answer your question.
Regards,
Andrey

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Niclas,
Thanks for your response. I am new to this forum and realised I posted in "new to qliksense' but I am actually using Qlikview so hoping the syntax is the same
I have added
Week(Date) as weeknumber
in my load script
and used this in my expression in my straight table
Rangesum(Above(Sum({<statuscodename={'Registered'},vrc_eventreportgroupname={'Other Public Marquees and Dining','Corporate Marquees','Public Dining'}>}Number_Registered*Unit_Price),0,$(=Max(weeknumber)))
Is that correct? I find the syntax of the set analysis a bit daunting!


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
As long as the end result is correct then you should be good . If you want to to be able to change the start week (accumulated from week A to week B, change out the '0' to $(min(weeknumber).
It appears as though you are lacking an end parenthesis in your Rangesum expression (see the red open parenthesis) so you would want to check that out.
Also, if you find everything working, please mark my answer as correct response so others can find it too!


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Also note that if your loading from Excel and the weeknumber is a column, Qlik treats this as text, so you'd need to use num#(weeknumber) to get a true number from it.
