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: 
Not applicable

Extreme date manipulations

Hello everyone!

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.

  

Calendar TypeDateYearQuarterMonthWorkday
Regular30-12-1520154120
Company30-12-1520154120
Regular31-12-1520154121
Company31-12-152016111

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


My questions:

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.



3 Replies
swuehl
MVP
MVP

Chiwa1,

if I understood your post correctly, what you are asking for should be quite usual and straight forward in QlikView / QS.

I am not sure how much experience you have with Qlik data models and how Qlik's associative data model works.

I would suggest that you create a master calendar with all dates needed just as you described above. This calendar should link to the sales fact table by Date field.

Then all you need to do is: make selections

a) Create a list box for Calendar Type and select a value, then open properties and enable 'always one selected value', to make sure that only one calendar is used at any time.

b) If you want, create more list boxes for Date, Month, Year,, etc.

You can select ranges e.g. by using searches:

The Search String

Or by using calendar objects or variables and maybe using triggers to use the variables for date range selections.

There are lot of examples here in the forum on how to do that.

In the list box for Workday, either select 1, or clear the selection to have both values active in the record set.

Create charts with calendar fields as dimension and expressions to aggregate your sales facts as needed.

You should already be set up to do all analysis you required.

You can automate some reports by using set analysis, i.e. defining 'static' filters in your charts.

If you have not started with QlikView or QlikSense, I would suggest that you go though some basic tutorial (available for free on the Qlik site), You should be ready to go within some hours / few days.

Hope this helps,

Stefan

Not applicable
Author

Hi,

Thanks for the tips!

I managed to do some of the stuff I wrote, by creating a YTD flag in the load script.

The only thing I want to achieve but still haven't figured out how is to create a Year to date total work days for each date in the calendar

I'm loading all the columns and want to calculate the Year to Date - SUm Work Days column during the load script.

Please note the Year, Month and Day are loaded for each row and varies depending on the calendar.

Totally lost in achieving this,


Thanks

  

Calendar TypeDateYearQuarterMonthDayWorkdayYear to Date - SUM -Work Days
Regular30/12/20152015412300NA
Company30/12/20152015412320NA
Regular31/12/20152015412311NA
Company31/12/2015201611111
Regular01/01/2016201511111
Company01/01/2016201611212
swuehl
MVP
MVP

Maybe something like (assuming the calendar is sorted by Date asc.):

LOAD

     If(Year = Year(Today(1)) AND Workday = 1, Autonumber(Date, [Calendar Type]),'NA') as [YTD Workdays],

     ...

RESIDENT YourCalendar;