topic Re: How to show two lines based on two fields based on two different types of dates in QlikView

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

Anonymous — Fri, 20 May 2011 17:11:44 GMT

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

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

johnw — Sat, 21 May 2011 00:19:03 GMT

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.

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

Anonymous — Sat, 21 May 2011 05:42:02 GMT

Hi John,

Thanks for your reply, I will try your suggestions.

Regards,

Henco

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

Anonymous — Mon, 23 May 2011 19:22:31 GMT

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

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

johnw — Mon, 23 May 2011 23:02:21 GMT

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.

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

Glenn_Renwick — Thu, 18 Aug 2011 07:49:33 GMT

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?

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

johnw — Thu, 18 Aug 2011 19:31:47 GMT

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

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

Thu, 01 Mar 2012 14:17:00 GMT

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

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

johnw — Thu, 01 Mar 2012 18:27:17 GMT

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).

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

johnw — Thu, 01 Mar 2012 20:08:00 GMT

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.

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

Thu, 22 Mar 2012 15:51:41 GMT

Hi John,

I am facing a problem to resolve a loop. I have created two qvw to explain my problem. I have following entities in my database:

Hospital

Brand: Which are known as drugs.

TA: Therapeutic Area, One therapeutic Area can have multiple drugs but one drug have only one TA

Access: Users have access to limited hospitals

Direct Sales: Sales of a Drug (Brand) through a hospital

MAT: Moving anual total for a drug and a hospital

Donation: A hospital is given some amount of donation for a TA

Now in first qvw, a synthetic table is being created which i have removed in second qvw by manually creating link table between brand and hospital.

The only problem which I was not able to solve is the loop which is getting created between hospital, TA, Donation and Brand as I have to analyze Donation amount by Hospital and TA. Currently the chart it is giving correct result but I don't know when loosly coupled table will start giving wrong results.

Hope I am able to explain my problem.

Anosh Nathaniel

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

johnw — Thu, 22 Mar 2012 16:27:38 GMT

Well, first, synthetic keys are not always bad, and I sometimes have one or two in my data models. Replacing one with your own concatenated key is, in my opinion, usually a waste of both your and the computer's time. Yes, I'm aware that people are taught otherwise in QlikView classes, and that the reference manual specifically suggests replacing them. For my opinion and subsequent discussion, see this thread. Note that I'm linking you to a post in the middle of the thread. If you want the whole discussion, including experts that disagree with me, I'd read that post, then go back to page 1 and start over from the top.

As for loops or loosely-coupled data, though, I never allow them in my data models. I can't really say what the right model for your data would be. I'd need a better understanding of your data, and time to devote that I just don't have. One possibility would be a central fact table concatenating, say, the four tables with the HospitalId in them and adding some sort of type field to distinguish between the types of data. Something else may be more appropriate, though.

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

Sun, 25 Mar 2012 22:05:18 GMT

I have four dates, two are in one table on rest are on individual tables. I want to join all four date to a Master Calender. I tried your above example using link table, first it was complaining about the loop and second thing performance issue. I have been trying to combining two dates using below code, prformance issues.... thanks for your help....

Link:

LOAD

Distinct ClaimID,

date(FirstDateOfService, 'MM/DD/YYYY') as Date,

'Encounter' as DateType

RESIDENT Claim

;

//CONCATENATE (Link)

//LOAD

//ClaimID,

//FirstBillingDate as Date,

//'Billing' as DateType

//RESIDENT Claim

//;

CONCATENATE (Link)

LOAD

ServiceLocationExpenseID,

ExpenseDate as Date,

'Expense' as DateType

RESIDENT ServiceLocationExpense

;

Calendar:

LOAD * ,

date(monthstart(Date),'MMM YYYY') as Month

;

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

Wed, 04 Apr 2012 07:47:37 GMT

Hi John,

I consulted with my friend and found a solution in which I have created Hospital-Brand-TA link table and all other table are joined through this link table. Attached is the solution qvw.

But now the problem is if I don’t get HospBrandLink table from db I need to generate it manually so that I can create ‘Link’ table. Hospital brand relationship is present in multiple tables like Direct Sales, MAT etc… I need to take all these table into consideration and need to find all possible combination of hospital – brand ids present in db.

Am I thinking in correct direction and Is this solution correct?

Thanks in advance,

Anosh Nathaniel

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

Thu, 17 Sep 2015 09:13:51 GMT

Hi John or anyone how can open qvw files,

Could you please share the load script? I have Qlik Sense so I can't open qvw files.. (very annoying).

Thank you,

Pablo

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

imsushantjain — Wed, 30 Aug 2017 06:32:02 GMT

Hi John!

I have a problem similar to Henco Van, but i have single fact table and i cant bifurcate it into two.

Order ID	TransactionDate	PostDate	Sales	Forecast
1	01-Aug-17	01-Aug-17	100	110
2	01-Aug-17	02-Aug-17	200	190
3	02-Aug-17	03-Aug-17	300	310
4	03-Aug-17	04-Aug-17	150	160
5	03-Aug-17	05-Aug-17	250	220
6	03-Aug-17	06-Aug-17	350	320
7	04-Aug-17	04-Aug-17	400	370

Can i achieve the same output as requested by Henco Van?

I know this goes against pricipal of QlikView, but ideally that islanddate should be able to toggle associations with TransactionDate and PostDate to calculate respective KPI!

Any work around to achieve the same?

Thanks

Sushant