topic Re: Extreme date manipulations in QlikView

Extreme date manipulations

Tue, 05 Apr 2016 19:43:51 GMT

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 Type	Date	Year	Quarter	Month	Workday
Regular	30-12-15	2015	4	12	0
Company	30-12-15	2015	4	12	0
Regular	31-12-15	2015	4	12	1
Company	31-12-15	2016	1	1	1

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.

Re: Extreme date manipulations

swuehl — Tue, 05 Apr 2016 21:55:43 GMT

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

Re: Extreme date manipulations

Fri, 29 Apr 2016 13:28:12 GMT

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 Type	Date	Year	Quarter	Month	Day	Workday	Year to Date - SUM -Work Days
Regular	30/12/2015	2015	4	12	30	0	NA
Company	30/12/2015	2015	4	12	32	0	NA
Regular	31/12/2015	2015	4	12	31	1	NA
Company	31/12/2015	2016	1	1	1	1	1
Regular	01/01/2016	2015	1	1	1	1	1
Company	01/01/2016	2016	1	1	2	1	2

Re: Extreme date manipulations

swuehl — Sun, 01 May 2016 21:18:08 GMT

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;