How to show two lines based on two fields based on two different types of dates

[Anonymous]

Hi all,

I've got a challenge here

I have two different types of dates. The first one is transactiondate (or invoicedate) and the second one is postdate (like pubdate).

In QlikView I have a line chart which shows me the sum of sales. These sales are based on transactiondate and is a sum of transamount.

X axis = TransMonth

Y axis = sum of sales

What I would like to do is to show the sum of sales forecast. This forecast can only be based on the postdate of course.

The problem is that I use the TransMonth on the X axis and I think I need to keep it that way, because of my sum of sales which is invoiced.

I would like to see two lines in my chart.

1. sum of sales based on the fields transactiondate and transamount

2. sum of sales based on the fields postdate and amount

On the X axis I would like to see 12 months.

On the Y axis I would like to see the sum of sales.

Is there anyone who have some great ideas on this?
Thanks in advance.

Henco

[johnw]

If you don't have enough data to cause a performance problem a date island is a common solution.  Create a third set of dates, then sum(if()) to match up your real dates:

dimension    = islanddate
expression 1 = sum(if(transactiondate=islanddate,transamount))
expression 2 = sum(if(postdate=islanddate,amount))

If that does have performance problems, a more complicated approach that should render much faster is to create a linkage table linking a master date to records that have either a matching transaction date or post date.

LinkageTable:
LOAD
transactiondate as date
,'transaction' as datetype
,recordID
RESIDENT YourTable
;
CONCATENATE (LinkageTable)
LOAD
postdate as date
,'post' as datetype
,recordID
RESIDENT YourTable
;

And then:

dimension    = date
expression 1 = sum({<datetype={'transaction'}>} transamount)
expression 2 = sum({<datetype={'post'}>} amount)

Hopefully I got that right.  You probably won't need the set analysis part if you don't have transamounts and amounts on the same records.

[Anonymous]

Hi John,

Thanks for your reply, I will try your suggestions.

Regards,

Henco

[Anonymous]

Hi John,

I have tried the first solution and it workst. But, as you mentioned, the performance is a problem right now.

So, after that I tried to make a Link table with a mastercalendar, but unfortunately I cant get it done.

I have two tables from where the dates are:

TRANSACTION

%INSERTION_ID

Transdate

Other transaction fields

PUBLICATION

%INSERTION_ID

Pubdate

Other publication fields

Could you please explain me more about making a link table?

Thanks in advance.

Kind regards,

Henco

[johnw]

I've attached a working example.  It has a bit more than you need, as it has three data tables, and two tables share an ID while the third uses a different ID.  But if you ignore OtherTable, I think it's basically your situation.  Records are linked to a single master calendar using a linkage table.

[Glenn_Renwick]

Hi John,

Good solution. I was trying to put a simple button on your app to do a basic data macro action based on your master date field.

Something like:

Action: Select in field

Expression: ='<=' & date(today()-1) & '>=' & date(today() - 28)

But it just doesn't populate my selections - Any ideas why?

[johnw]

The data in the example only goes up to June 30.  There's nothing to select in the range you're using.

[#8]

Hi John,

I have seen your example qvw and I am a little bit confused. I have attached a modified version of your qvw application in which there is no need to create linkage table still we can achieve the desired result.

Please look into it and explain me whether it is correct or not?

After reading hencovanee post I thought that both of dates (transaction & publication) are present in the same table as two different fields. If they were then date island would be the perfect solution. But if it gives performance problem then what should be other solution?

I am asking this question because I am facing the same problem in one of my assignment where there are multiple dates are present in a table as different fields and I need to link them with master calendar table.

Thanks in advance,

Anosh Nathaniel

[johnw]

The more complicated data model still supports everything we were able to do before we added the combined calendar, because it still has all that data.  All of that information has been preserved.  The simpler data model throws away a lot of possibly valuable information in order to link the dates together.  If we don't need the original information, then throwing it away and using the simpler model is almost certainly a better approach.  But I assumed we'd want to keep track of the fact that ID 1 in the first two tables was the same thing, and that a created date means something different than a departure date.

To make it more clear that ID has more than one date associated with it, I've added an ArrivalDate to Table2.  To make it more clear that Table1 and Table2 should remain separate tables, I've changed Table1 to an activity table, with two activities on ID 1.  Then I created a third example chart that DOESN'T use our new linkage table and combined calendar to show that we still support the original information and the kind of charts we might want to build with it.

But I should be clear that there's no one RIGHT data model for all purposes.  How to model your data is entirely dependent on what your data means and how you want to interact with it.  All I'm giving here is an example, one way to link dates to a common calendar while preserving all of the original information intact (and in this case, with no change to its structure either).

[johnw]

Oops, I introduced a bug.  Since Table1 now has multiple rows per ID, we need another field to have a complete key.  I've added a sequence field since we might have multiple activities on a single date.  Since the linkage table has to specify the full key, it now also specifies the sequence.  This creates a synthetic key.  That should be fine in this case, because I do indeed want these tables connected by two fields.