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

[#2]

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

[johnw]

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.

[#4]

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

;

[#5]

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

[#6]

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

[imsushantjain]

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