I'm very to new to Qlikview, coming from legacy IT and BI systems.
looking forward to see the community in action.
I spent sometime researching the forum and the web but couldn't the info I was looking for.
a robust, agile and reusable solution to manipulate sales data by two calendars, date ranges and actual work day.
This is going to be rather long, bear with me if you can.
The main table:
1. Sales data - basically -> SalesDate | SalesPrice | ProductCode
Here is the situation, I would like to have an extremely flexible and easy way to filter sales data as follows.
I want to be able to change instantly between two calendars (Regular and Company), and to combine traditional periods such as quarter and months with date ranges.
For each date I store in a master calendar table two rows, to list if it's a working day (value between 0 and 1) and to which periods it belongs, here's an example.
note that a date can have a completely different periods in the company calendar.
The ideal user experience as I see it:
At the top of the sheet the user will select
1. Select calendar: Company/Regular
2. Start Date:(DD-MM): 01-01
3. End-Date:(DD-MM): 31-12
4. Select comparison: dates/workday
After selections are made and every time they change the Sales data field will filter accordingly: (leaving only relevant rows from sales data)
My thinking for solution is:
1. Write a function/formula like this:
IsInRange(SalesDate,CalendarType,StartDate,EndDate,Workday (Y/N) )
so four kind of checks are required:
1. if it's a regular calendar and not by workdays. just check if date is between the range for that year
2. if it's a regular calendar and by workdays. check the sum of total work days in year from year start until StartDate and up until EndDate, and check if the total of working days from year start to SalesDate is in between.
3.The same as 1 but with Company calendar
4. the same as 2 but with company calendar
2. sum the sales in all charts with the formula SUM(IsINRange(SalesDate)= True, SalesPrice)
3. create trigger for all the var listed above for data refresh
1. is that a good solution in your opinion, would you do it differently ?
2. should I worry about performance and consider losing flexibility by calculating YTD flags in load instead?, sales data contains 1 Million rows.
3. How do I write a formula like this, can you please refer me in the right direction?
4. How do I force a re-calculation for every change
Thanks for reading, I would love to hear your comments.