Combine sales history in excel by week number to current year from SQL

[#1]

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?

[niclaz79]

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.

[ahaahaaha]

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

[#4]

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!

[niclaz79]

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!

[dwforest]

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.