topic Re: Show data based on 2 dates in QlikView

Show data based on 2 dates

Mon, 26 Aug 2013 06:44:37 GMT

Hello,

I am using qlikview 11.

I have 2 dates in the report, Order date and Commitment Date.

I have master calendar which is linked to Order date.

So displaying data in pivot table based on Order date is not a problem and it is working fine.

But now i have to create another pivot table, where i have to show the data based on Commitment date.

Since my calendar is linked with order date, the data that it shows based on commitment date basis is incorrect.

Please can anyone help me on this, as how to show data based on order date as well as commitment basis.

Re: Show data based on 2 dates

tresB — Mon, 26 Aug 2013 06:55:57 GMT

Probably you are taking master calendar date field in the pivot table as dimension. Try taking the commitment date field as dimension in the pivot table instead.

Re: Show data based on 2 dates

Mon, 26 Aug 2013 07:34:21 GMT

Hi tresesco,

I have added order date in my pivot table and order date is linked with calendar.

I tried another way also as follows:

i created master calendar which i did not link to actual data.

Then in the expressions i wrote

for order date basis,

sum({< Order = {'>= $(=monthstart(min(calendarDate))) <= $(=monthend(min(calendarDate)))'} >} OrderQty)

for commitment date basis,

sum({< CommitmentDate= {'>= $(=monthstart(min(calendarDate))) <= $(=monthend(min(calendarDate)))'} >} OrderQty)

This is working fine.

But this has affected the performance.

If you can help me to solve this performance issue, this will also do.

Re: Show data based on 2 dates

Mon, 26 Aug 2013 07:40:41 GMT

Hey dude,

I would build a date link table, and in your set analysis call that datetype. There is a good post from Rob somewhere on this forum, will try and find it for you, but none the less this is the process you need to follow to use multiple date fields with same data model

1) Create new tab in script and call it Date Link or something you will recognise

2) Your first table script should look something like this

DateLink:

LOAD DISTINCT

OrderDate as CalendarDate,

'Order' as DateType

RESIDENT OrderDateTable;

CONCATENATE (DateLink)

LOAD DISTINCT

CommitmentDate as CalendarDate

'Commitment' as DateType

RESIDENT CommitmentTable;

As you can see we are creating a table with a field that helps us identify which date belongs to which original field

3) Call you calendar last! Since in your master calendar script you are generating the dates in between your min and max, you must run it last. You will change the field you want to base the calendar on from ORDERDATE to CALENDARDATE or what ever you named the date field above in the concatenate statement

4) In your pivot table, you can now sort by expression. In your expression you could write something like

=sum({<DateType={'Commitment'}>}Sales)

If you just want another pivot table with Commitment date only, then in your dimension expression write this

=if(DateType = 'Commitment',CalendarDate,)

Make sure you check the box suppress nulls since you don't want your expression to evaluate the dates you excluded in the null return. Let me know if you come right mate and this is what you were looking for

Cheers,

Byron

Re: Show data based on 2 dates

Mon, 26 Aug 2013 07:42:10 GMT

The previous method I mentioned about date link will definitely resolve the performance issue created by your new expression as you will not have a resource intensive set expression to manipulate

Re: Show data based on 2 dates

Mon, 26 Aug 2013 07:48:55 GMT

Here's the link for Rob's answer

http://community.qlik.com/message/273796#273796

Cheers,

Byron

Re: Show data based on 2 dates

Mon, 26 Aug 2013 11:00:24 GMT

Hi Byron,

I have tried the way you told.

First took data from database with fields as Order date and commtiment date.

Then created link table as follows:

DateLink:

LOAD DISTINCT

OrderDate as CalendarDate,

'Order' as DateType

RESIDENT OrderDateTable;

CONCATENATE (DateLink)

LOAD DISTINCT

CommitmentDate as CalendarDate

'Commitment' as DateType

RESIDENT OrderDateTable;

Next created the master calender with date field as CalendarDate.

Now when I write the expression as

sum({< dateType = {'Order'} >} OrderQty)

I dont get the correct value.

Re: Show data based on 2 dates

Mon, 26 Aug 2013 14:22:19 GMT

Hi Mate,

Well without seeing a sample of your data model, I can't really see what could be the problem.

If OrderQty exists in the same fact table as the calendar date then the calculation should have worked correctly with the above expression you used. Check what dimension you have in your object. If it is date, try the if statement, even though in theory the set analysis should cater for the new link table.

I suggest you upload a sample with what the expected output should be and lets have a look

Cheers,

Byron